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

[Gerhard Killesreiter]

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.

Cheers,
	Gerhard