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

[Derek Wright]

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, 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.

any further insight into this problem would be most appreciated (i'm  
still relatively new to all this DB query optimization stuff).  feel  
free to post ideas (and patches!) directly into http://drupal.org/ 
node/74238.

thanks,
-dww