Last modified: 2012-11-18 12:44:22 UTC
The following query type can be slow on commons, especially if globalimagelinks isn't in ram (in which case it can take 80 seconds instead 1): mysql> explain SELECT /* GlobalUsageQuery::execute */ gil_to,gil_wiki,gil_page,gil_page_namespace_id,gil_page_namespace,gil_page_title FROM `globalimagelinks` WHERE gil_to = 'Flag_of_France.svg' AND (gil_wiki != 'dewiki') ORDER BY gil_to ASC, gil_wiki ASC, gil_page ASC LIMIT 51\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: globalimagelinks type: range possible_keys: PRIMARY,globalimagelinks_wiki,globalimagelinks_wiki_nsid_title key: PRIMARY key_len: 291 ref: NULL rows: 1544156 Extra: Using where; Using filesort 1 row in set (0.00 sec) The primary key of globalimagelinks is: PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`), which means the data is naturally stored in "gil_to ASC, gil_wiki ASC, gil_page ASC" sorting. The ORDER BY doesn't actually effect the query response but does still trigger the filesort. In my tests, removing it resulted in a 10x speed increase, and matching results.
So you're just suggesting drop the explicit order by if it's ASC, but add it if it's DESC? https://gerrit.wikimedia.org/r/29524
mysql> explain SELECT gil_to,gil_wiki,gil_page,gil_page_namespace_id,gil_page_namespace,gil_page_title FROM `globalimagelinks` WHERE gil_to = 'Flag_of_France.svg' AND (gil_wiki != 'dewiki') ORDER BY gil_to ASC, gil_wiki ASC, gil_page ASC LIMIT 51\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: globalimagelinks type: range possible_keys: PRIMARY,globalimagelinks_wiki,globalimagelinks_wiki_nsid_title key: PRIMARY key_len: 291 ref: NULL rows: 1371752 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain SELECT gil_to,gil_wiki,gil_page,gil_page_namespace_id,gil_page_namespace,gil_page_title FROM `globalimagelinks` WHERE gil_to = 'Flag_of_France.svg' AND (gil_wiki != 'dewiki') LIMIT 51\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: globalimagelinks type: range possible_keys: PRIMARY,globalimagelinks_wiki,globalimagelinks_wiki_nsid_title key: PRIMARY key_len: 291 ref: NULL rows: 1371752 Extra: Using where 1 row in set (0.00 sec) mysql> explain SELECT gil_to,gil_wiki,gil_page,gil_page_namespace_id,gil_page_namespace,gil_page_title FROM `globalimagelinks` WHERE gil_to = 'Flag_of_France.svg' AND (gil_wiki != 'dewiki') ORDER BY gil_to DESC, gil_wiki DESC, gil_page DESC LIMIT 51\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: globalimagelinks type: range possible_keys: PRIMARY,globalimagelinks_wiki,globalimagelinks_wiki_nsid_title key: PRIMARY key_len: 291 ref: NULL rows: 1371752 Extra: Using where; Using filesort 1 row in set (0.00 sec)
(In reply to comment #1) > So you're just suggesting drop the explicit order by if it's ASC, but add it if > it's DESC? > https://gerrit.wikimedia.org/r/29524 Status Merged
