[Labs-l] Query takes forever on labsdb

[Jaime Crespo]

On Sat, Aug 29, 2015 at 10:36 PM, Alex Monk <krenair at gmail.com> wrote:

> Looks like it works on labsdb1001, but not labsdb1002 and labsdb1003.
>

Yes, there is nothing wrong with the databases, such as corruption or
something similar, but MySQL choses a less optimal plan for 2 and 3, as you
can see on this explain, going from 15 second execution time to "forever":

MariaDB LABS labsdb1002 fawiki_p > EXPLAIN select page_title
    -> from page
    -> join category on page_title = cat_title
    -> left join categorylinks on page_title = cl_to
    -> left join templatelinks on tl_from = page_id and tl_namespace = 10
and tl_title in ('رده_خالی' , 'رده_بهتر')
    -> where page_namespace = 14 and cl_to is null and tl_title is null
    -> group by page_title\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: page
         type: ref
possible_keys: name_title
          key: name_title
      key_len: 4
          ref: const
         rows: 322989
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
         type: eq_ref
possible_keys: cat_title
          key: cat_title
      key_len: 257
          ref: fawiki.page.page_title
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: categorylinks
         type: ref
possible_keys: cl_timestamp,cl_sortkey
          key: cl_timestamp
      key_len: 257
          ref: fawiki.page.page_title
         rows: 72785
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: templatelinks
         type: ref
possible_keys: tl_from,tl_namespace,tl_backlinks_namespace
          key: tl_namespace
      key_len: 4
          ref: const
         rows: 383047
        Extra: Using where; Using index
4 rows in set (0.01 sec)




MariaDB LABS labsdb1001 fawiki_p > EXPLAIN select page_title
    -> from page
    -> join category on page_title = cat_title
    -> left join categorylinks on page_title = cl_to
    -> left join templatelinks on tl_from = page_id and tl_namespace = 10
and tl_title in ('رده_خالی' , 'رده_بهتر')
    -> where page_namespace = 14 and cl_to is null and tl_title is null
    -> group by page_title\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: page
         type: ref
possible_keys: name_title
          key: name_title
      key_len: 4
          ref: const
         rows: 340784
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
         type: eq_ref
possible_keys: cat_title
          key: cat_title
      key_len: 257
          ref: fawiki.page.page_title
         rows: 1
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: categorylinks
         type: ref
possible_keys: cl_timestamp,cl_sortkey
          key: cl_timestamp
      key_len: 257
          ref: fawiki.page.page_title
         rows: 10
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: templatelinks
         type: ref
possible_keys: tl_from,tl_namespace,tl_backlinks_namespace
          key: tl_from
      key_len: 8
          ref: fawiki.page.page_id,const
         rows: 16
        Extra: Using where; Using index
4 rows in set (0.00 sec)

As a workaround, for now, please use labsdb1001, instead of default host
for fawiki for this particular query. Sadly, you cannot use FORCE/USE index
on views- I will try to reanalyze the table statistics over the next week
to see if that fixes this particular query.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20150830/61fe15a4/attachment.html>