Page MenuHomePhabricator

Optimize the Content translation stats query with an index
Closed, ResolvedPublic1 Estimated 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.

Event Timeline

santhosh raised the priority of this task from to Needs Triage.
santhosh updated the task description. (Show Details)
santhosh added a subscriber: santhosh.
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

@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 :)

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 moved this task from Backlog to Blocked on the LE-Sprint-84 board.
Nikerabbit edited a custom field.
Nikerabbit moved this task from Blocked to Done on the LE-Sprint-84 board.