https://bugzilla.wikimedia.org/show_bug.cgi?id=24479
--- Comment #10 from Aryeh Gregor <simetrical+wikib...@gmail.com> 2010-12-31 18:21:21 UTC --- (In reply to comment #7) > mysql> describe SELECT cr_id,cr_repo_id,cr_status,COUNT(DISTINCT cc_id) AS > comments,cr_path,cr_message,cr_author,cr_timestamp FROM > `mw_code_tags`,`mw_code_rev` LEFT JOIN `mw_code_comment` ON ((cc_repo_id = > cr_repo_id AND cc_rev_id = cr_id)) WHERE cr_repo_id = '3' AND (ct_repo_id = > '3') AND (cr_id=ct_rev_id) AND ct_tag = 'api' GROUP BY cr_id ORDER BY cr_id > DESC LIMIT 51; > +----+-------------+-----------------+--------+-----------------------------------+------------+---------+--------------------------------------------------------+------+-----------------------------------------------------------+ > | id | select_type | table | type | possible_keys > > | key | key_len | ref > > | rows | Extra | > +----+-------------+-----------------+--------+-----------------------------------+------------+---------+--------------------------------------------------------+------+-----------------------------------------------------------+ > | 1 | SIMPLE | mw_code_tags | ref | PRIMARY,ct_repo_id > > | PRIMARY | 4 | const > > | 1 | Using where; Using index; Using temporary; Using filesort | > | 1 | SIMPLE | mw_code_rev | eq_ref | > PRIMARY,cr_repo_id,cr_repo_author | PRIMARY | 8 | > const,wikidb.mw_code_tags.ct_rev_id | 1 | > > | > | 1 | SIMPLE | mw_code_comment | ref | cc_repo_id,cc_repo_time > > | cc_repo_id | 8 | > wikidb.mw_code_rev.cr_repo_id,wikidb.mw_code_rev.cr_id | 1 | Using index > > | > +----+-------------+-----------------+--------+-----------------------------------+------------+---------+--------------------------------------------------------+------+-----------------------------------------------------------+ > 3 rows in set (0.00 sec) This is a classic pitfall for the MySQL optimizer. What version are you using? On the toolserver, 5.1.53, it avoids the filesort. The problem is that you have a few separate conditions that you need indexes for, aside for join conditions and assuming MySQL is smart enough to do equality propagation (not always a good bet): ct_tag = 'api', ct_repo_id = '3', and the order by/group by. No one index will work for all of them, so MySQL has to choose which index it wants to use. Since there's a LIMIT, the filesort option is probably the worst, but it chooses that option anyway. Often in this case a FORCE INDEX will fix the problem. On the toolserver, it's smart enough to select from code_rev first and not do a filesort, and then do extra scanning on code_tags. In this case, your best bet is probably to add an index on (ct_repo_id, ct_tag, ct_rev_id), if this query is a problem in practice after you stop it from filesorting. If you're not ever ordering or grouping by ct_tag, you can just change the primary key (but I'd guess you sometimes do group by it). I'd also clean up the query a bit to avoid relying on equality propagation, changing "cr_repo_id = '3'" to "cr_repo_id = ct_repo_id" and changing "GROUP BY cr_id ORDER BY cr_id DESC" to "GROUP BY ct_rev_id ORDER BY ct_rev_id DESC". But the latter part is probably not necessary, just makes it clearer how you expect the query to execute. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are on the CC list for the bug. _______________________________________________ Wikibugs-l mailing list Wikibugs-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
