[drupal-devel] Fwd: history-table

[Gerhard Killesreiter]

On Mon, 9 May 2005, Dries Buytaert wrote:

> Begin forwarded message:
>
> > From: Dries Buytaert <dries at 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