[development] Drupal.org slow queries analyzed with explain: CVS tables

Gerhard Killesreiter gerhard at killesreiter.de
Tue Aug 1 01:05:14 UTC 2006

Derek Wright wrote:
> On Jul 31, 2006, at 2:57 PM, Kieran Lal wrote:
>> Here's what I think is happening.
>> cvs_messages has 25K rows and is joining on cvs_repositories which has 
>> two rows, one for core and one for contrib.
>> Then that is joined against users table which has approximately 80K 
>> users.
>> 25K joined on 2 joined on 80K.   I am wondering if we can just get the 
>> values from the cvs_repositories into a PHP array and then join 
>> cvs_messages to users on uid.  Any ideas would be welcome.
> i had killes run this query w/ and w/o the INNER JOIN on 
> {cvs_repositories}.  he posted the results in 
> http://drupal.org/node/74238 (the issue in the cvs.module's queue about 
> fixing all this stuff).  the results are that we end up with a tablesort 
> on the query in both cases.  so, i don't think re-organizing the code to 
> do the equivalent of the INNER JOIN in php code is going to help.  i 
> think this is just a big, nasty, slow query, and i'm not sure there's 
> much we can do about it.
> furthermore, killes already added caching,

Well, turns out my caching didn't cache a single query. :p
The cache keys for insert and retrieval did not match. :p
But it does now work, so I think we can put this case to rest.

> so if we're trying to do the 
> identical query, we just lookup the results from the cache.  apparently 
> there are something like 320 cached queries in d.o's DB in the last 
> day.  so, at least that much is working.

That number was the number of executed _and_ logged queries. We only log 
only every tenth query, though.


More information about the development mailing list