Hi, I don't know a direct way around it, but depending on the sort of environment you are working in, you can try different storage engine: - InnoDB will lock less data, so it may perform better with many processes, however if you lock in different directions you may get deadlocks - Memory will update very fast, but there are some issues with server reboots and you need to have everything stored in memory. This could be feasible for the 10% subset of your forum that is actually being looked at (barely anyone looks at old posts) - (I think) Falcon engine has fixed write performance, not sure about reads, but you have more options to optimise read performance (for example with caching)
One other thing to think of is what percentage of your table is being read regularly. If this is too big (> about 15%) your indexes won't work. use a technique like this to get around it: http://dba.stackexchange.com/questions/4191/mysql-query-optimization-indexing-and-pagination/4192#4192 Your user_id index part will probably get your around this, unless your power users read all posts (not unlikely in a forum) You could also look at nested sets to store your forum topics and build the "read" table as user_id|topic_id|message_id HTH, Jochen On 8 August 2013 15:56, matt thomson <[email protected]> wrote: > Hi everybody, > > I'm working on a forum like web app where I record what topics have been > read by a logged in user. Usually for a many-to-many relation like this, I > would use a 3rd table, and have a user_id and a topic_id column in the > table. I'm not sure if this is scalable in this case though, the table will > meet three conditions: > > It will be written to on almost most page views (to say the current topic > has been read). > It will be read from on most page views (to check which topics have been > read and make this in the html). > It may get very large (eg 1 million+ records). > > Usually, if a table is going to be this big and queried a lot, I will put an > index on the columns that are queried. However, as well as being read from > frequently, the table is also being written to frequently. As indexes take > time to update on write, then updating the index constantly might end up > slowing the table down so much that this method is not scalable. > > Does anyone know a way around this issue? > > Thanks. > > -- > -- > NZ PHP Users Group: http://groups.google.com/group/nzphpug > To post, send email to [email protected] > To unsubscribe, send email to > [email protected] > --- > You received this message because you are subscribed to the Google Groups > "NZ PHP Users Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > -- P.S.: Newsletter - The business case for custom built software - http://eepurl.com/w0Kcv Kind Regards, Jochen Daum "There is no shortcut to anywhere worth going" - Beverly Sills Automatem Ltd Tauranga: +64 7 281 1289 Auckland: +64 9 630 3425 Mobile: +64 21 567 853 Email: [email protected] Website: www.automatem.co.nz Skype: jochendaum http://nz.linkedin.com/in/automatem http://twitter.com/automatem -- -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] --- You received this message because you are subscribed to the Google Groups "NZ PHP Users Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
