Reduce queries on typical ApiQueryTranslationsAids api call

Authored by Nikerabbit on Dec 12 2017, 6:47 PM.


Reduce queries on typical ApiQueryTranslationsAids api call

This replaces hundreds of queries with just single database query.
The actual amount of queries depends on how many translations for
a message exists in the database.

There where multiple issues:

  • The static cache in TranslationAid::getTranslations did not work. It seemed like it should have worked, maybe HHVM bug but not sure. This caused all queries to be executed twice.
  • There was one query to find all the page titles for translations, and a second query to load the text contents.
  • After that we would query the revtag table once for each language
  • We would also query the message index once per each language.

The fix is to create a new class that caches the query results.
Instances of the new class is injected into the translation aids,
so the cache lifetime is nicely bound. The old static cache, if it
worked, could grow indefinitely and leak memory.

The new class does everything in one query and uses simple string
manipulation to avoid querying the message index.

The new query has the following query plan:

  • 1. row ******* id: 1 select_type: SIMPLE table: bw_page type: range

possible_keys: PRIMARY,name_title

    key: name_title
key_len: 261
    ref: NULL
   rows: 109
  Extra: Using index condition
  • 2. row ******* id: 1 select_type: SIMPLE table: bw_revtag type: eq_ref

possible_keys: rt_type_page_revision,rt_revision_type

    key: rt_type_page_revision
key_len: 70
    ref: const,translatewiki_net.bw_page.page_id,translatewiki_net.bw_page.page_latest
   rows: 1
  Extra: Using where; Using index; Not exists
  • 3. row ******* id: 1 select_type: SIMPLE table: bw_revision type: eq_ref

possible_keys: PRIMARY,rev_id,page_timestamp,page_user_timestamp

    key: PRIMARY
key_len: 8
    ref: translatewiki_net.bw_page.page_id,translatewiki_net.bw_page.page_latest
   rows: 1
  • 4. row ******* id: 1 select_type: SIMPLE table: bw_text type: eq_ref

possible_keys: PRIMARY

    key: PRIMARY
key_len: 4
    ref: translatewiki_net.bw_revision.rev_text_id
   rows: 1

Bug: T187165
Change-Id: I22f0bfdd948f3777bdecad85bf92719307da3a02