Oh, so you mean if I just use indexes correctly in mysql everything gets much faster? Who knew?
Am I crazy, or can anyone else confirm a speed up when loading the front page?
Much faster this morning. Good work!
For anyone interested in the details:
There is a table in the database called unread_comments. This table has columns for storing the user id number, the page id number, and the comment id number of every unread comment. When a new comment is added to the site, one new row is created in this table for every user subscribed to that page. Right now there are 113 users in the database (most of these inactive, but that doesn't matter here,) so for every new comment there are roughly 113 new rows added to the unread_comment table. When an individual user reads a new comment, one row is deleted from this table.
Yesterday I did some speed profiling of the front page and determined that finding new comment totals was taking up most of the time. Unscientifically, it was taking between .2 and .4 seconds to find the number of new comments for *each* page you had listed on your home page.
There are 181,848 rows in the unread_comment table. I had a lot of complex plans on how to decrease this number, but it turns out none of those are necessary. Without going into the details (which even I barely understand) I added an index to the unread_comment table which makes the calculations needed to build the front page much faster. The trade off (because there is *always* a trade off in this sort of thing) is that there will be a slight speed penalty when making a comment on the site. But I think the slow down in the individual case will be merely theoretical (I doubt it could be noticed by humans) while the compound speed up of calculating all the front page new comment totals actually does make a difference. I'm seeing about a 50% speed up, although again, that's an unscientific number.
thank you thank you. it's way better.
|
Am I crazy, or can anyone else confirm a speed up when loading the front page?
- jim 2-22-2004 7:10 pm
Much faster this morning. Good work!
- tom moody 2-22-2004 8:39 pm [add a comment]
For anyone interested in the details:
There is a table in the database called unread_comments. This table has columns for storing the user id number, the page id number, and the comment id number of every unread comment. When a new comment is added to the site, one new row is created in this table for every user subscribed to that page. Right now there are 113 users in the database (most of these inactive, but that doesn't matter here,) so for every new comment there are roughly 113 new rows added to the unread_comment table. When an individual user reads a new comment, one row is deleted from this table.
Yesterday I did some speed profiling of the front page and determined that finding new comment totals was taking up most of the time. Unscientifically, it was taking between .2 and .4 seconds to find the number of new comments for *each* page you had listed on your home page.
There are 181,848 rows in the unread_comment table. I had a lot of complex plans on how to decrease this number, but it turns out none of those are necessary. Without going into the details (which even I barely understand) I added an index to the unread_comment table which makes the calculations needed to build the front page much faster. The trade off (because there is *always* a trade off in this sort of thing) is that there will be a slight speed penalty when making a comment on the site. But I think the slow down in the individual case will be merely theoretical (I doubt it could be noticed by humans) while the compound speed up of calculating all the front page new comment totals actually does make a difference. I'm seeing about a 50% speed up, although again, that's an unscientific number.
- jim 2-22-2004 9:10 pm [add a comment]
thank you thank you. it's way better.
- sally mckay 2-22-2004 11:42 pm [add a comment]