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