Author: afeldman
Description:
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 filesort1 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.
Version: master
Severity: normal