https://bugzilla.wikimedia.org/show_bug.cgi?id=3311
--- Comment #70 from Tyler Romeo <[email protected]> --- Success! So the issue is that the cl_sortkey index on categorylinks puts the cl_to column before the cl_sortkey column, so when you add the "cl_to IN ...", it can no longer use the index to sort by cl_sortkey (from the ORDER BY clause). After adding the following index: ALTER TABLE `categorylinks` ADD UNIQUE `cl_newsort` ( `cl_type`, `cl_sortkey`, `cl_to`, `cl_from` ) And then running the following query: EXPLAIN EXTENDED SELECT `cl_from` FROM `categorylinks` INNER JOIN `page` ON `page_id` = `cl_from` LEFT JOIN `category` ON `cat_title` = `page_title` AND `page_namespace` = 14 WHERE `cl_type` = 'page' AND `cl_to` IN ( 'Foo', 'Test' ) ORDER BY cl_sortkey I finally got no more filesort. (I was even able to get rid of the FORCE INDEX usage.) If somebody could please check this and make sure I'm still sane, and that MySQL isn't just inventing things to trick my mind, that'd be great. -- You are receiving this mail because: You are on the CC list for the bug. You are the assignee for the bug. _______________________________________________ Wikibugs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
