https://bugzilla.wikimedia.org/show_bug.cgi?id=41283
Web browser: ---
Bug #: 41283
Summary: optimize globalimagelinks query
Product: MediaWiki
Version: 1.21-git
Platform: All
OS/Version: All
Status: NEW
Severity: normal
Priority: Unprioritized
Component: Database
AssignedTo: [email protected]
ReportedBy: [email protected]
Classification: Unclassified
Mobile Platform: ---
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.
--
Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
