Page MenuHomePhabricator

Optimize the Content translation stats query with an index
Closed, ResolvedPublic1 Estimate Story Points

Description

Niklas pointed out:

MariaDB [dev_translatewiki_net]> explain SELECT translation_source_language,translation_target_language,translation_status,count(translation_target_language) as count FROM bw_cx_translations GROUP BY translation_source_language, translation_target_language ORDER BY translation_status, translation_source_language ;
+------+-------------+--------------------+------+---------------+------+---------+------+------+---------------------------------+

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

+------+-------------+--------------------+------+---------------+------+---------+------+------+---------------------------------+

1SIMPLEbw_cx_translationsALLNULLNULLNULLNULL5Using temporary; Using filesort

+------+-------------+--------------------+------+---------------+------+---------+------+------+---------------------------------+
That is full table scan, dumping it into temporary file and sorting it as far as I understand. That does not scale.

We need a new index with translation_source_language, translation_target_language or change the current index to have them as prefixes.

Details

Related Gerrit Patches:
mediawiki/extensions/ContentTranslation : masterAdd index to speed up stats queries

Event Timeline

santhosh created this task.Feb 4 2015, 1:11 PM
santhosh raised the priority of this task from to Needs Triage.
santhosh updated the task description. (Show Details)
santhosh added a subscriber: santhosh.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 4 2015, 1:11 PM
Amire80 triaged this task as Medium priority.Feb 4 2015, 2:07 PM
Amire80 set Security to None.
gerritbot added a subscriber: gerritbot.

Change 188981 had a related patch set uploaded (by Nikerabbit):
Add index to speed up stats queries

https://gerrit.wikimedia.org/r/188981

Patch-For-Review

Change 188981 merged by jenkins-bot:
Add index to speed up stats queries

https://gerrit.wikimedia.org/r/188981

Pginer-WMF edited a custom field.Feb 24 2015, 2:30 PM
Arrbee edited projects, added LE-Sprint-83; removed LE-Sprint-82.Feb 24 2015, 3:23 PM
Arrbee moved this task from Backlog to Blocked on the LE-Sprint-83 board.Feb 25 2015, 2:43 PM

@Nikerabbit have you pinged @Springle to find out what is needed to apply this change in production?

I was planning to ping him as it is unclear how the process is supposed to work other than adding the tracker bug :)

I applied this on beta labs.

Nikerabbit renamed this task from Optimize the Content translation stats query to Optimize the Content translation stats query with an index.Mar 3 2015, 4:25 PM
Arrbee edited projects, added LE-Sprint-84; removed LE-Sprint-83.Mar 11 2015, 6:11 AM
Arrbee moved this task from Backlog to Blocked on the LE-Sprint-84 board.
Arrbee edited a custom field.
Arrbee moved this task from Backlog to Sprint 84 on the ContentTranslation-Release4 board.
Nikerabbit closed this task as Resolved.Mar 13 2015, 12:49 PM
Nikerabbit edited a custom field.
Nikerabbit moved this task from Blocked to Done on the LE-Sprint-84 board.