On Mon, 9 May 2005, Dries Buytaert wrote:
Begin forwarded message:
From: Dries Buytaert <dries@buytaert.net> Date: Sat 7 May 2005 13:45:43 CEST
From profiling drupal.org, it seems that updating entries in (or inserting entries into) the history-table can be quite slow. Turns out the table it pretty big, so I'm not sure it is missing an index or two.
mysql> select count(*) from history; +----------+ | count(*) | +----------+ | 123805 | +----------+ 1 row in set (0.00 sec)
I spent the past 2 days keeping an eye on this, and it is certainly a performance bottleneck. Querying or updating the history table takes 6 ms whereas other SQL queries take about 0.2 - 0.3 ms on average. That is, we can do 20 to 30 other SQL queries in 6 ms. Something to think about.
I think a possible "solution" would be to put the updating of the history table towards the end of a page request. Currently, the table is updated from node_show. If it were updated (with all neccessery node views) at the end of the page request, it would probably not be faster, but the user experience would be better (or do we only start to deliver the page after _everything_ has been processed?). So, in node_tag_new() we should only collect the nids in a static array and then later on node_tag_new(NULL, 'update') we could execute the queries (maybe bunching them together in a more effective way, we also do one query per history check in node_last_viewed(), Dries how bad is that query?). This could be invoked in node_footer(). Cheers, Gerhard