optimize globalimagelinks query
Closed, ResolvedPublic

Description

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 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.


Version: master
Severity: normal

Details

Reference
bz41283
bzimport raised the priority of this task from to Needs Triage.
bzimport set Reference to bz41283.
bzimport added a subscriber: Unknown Object (MLST).

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

Gilles moved this task from Untriaged to Done on the Multimedia board.Dec 4 2014, 10:11 AM
Gilles triaged this task as Unbreak Now! priority.
Gilles lowered the priority of this task from Unbreak Now! to Needs Triage.Dec 4 2014, 11:21 AM