Well, I'm not Scream but I'll try an answer anyway []/testimages/icons/smile.gif[/].
I read somewhere or other (and I can't find the reference now...) that if you divide a table into lots of tables you can hinder MySQL's ability to buffer things (and I suppose you can run out of file descriptors too). I don't know how to quantify the impact though.
I think I've read before that W3T used to use a separate table for each board but it was changed because some shared hosting accounts were not allowed to have so many tables (probably for the reasons above).
One problem with this approach is that if it fixes the problem it only does so temporarily. As the number of posts increases you will eventually hit the point where each board is individually as large as the entire forum is right now, and then you're out of luck--no way to further subdivide it. In contrast, with the method I proposed (cutting into "new" vs. "old" posts) the table that the insertions go into doesn't grow as the board gets older because you keep emptying it out (into the "old" table) periodically. Also, if you divide up the posts table by board and you have 10 boards you have reduced the size of the table where the insertions go by roughly a factor of 10 (assuming all boards are about the same size) whereas doing the new-old split allows you to make the insertion table arbitrarily small by increasing the frequency of sweeping the new posts into the old table (you probably have to shut down the forum when you do this, so once a day is probably the most that is practical). If your forum is 1 year old and you sweep every day, the insertion table will be roughly 365 times smaller than it is now.
P.S. Does anybody know why
this says that insertion time goes like N*log(N)? To me it seems like it should just be log(N) (i.e. the amount of time it takes to work through the tree to find the node where the insertion happens). Maybe they are talking about the time it takes to insert N items rather than the time it takes to insert 1 item when there are already N of them in the table (although that's really not the relevant thing in this context)?
Bill Dimm,
SaveTheFreeWeb.com
