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