That explains a lot. Unfortunately, we (regular uses of Labs) cannot run EXPLAIN to identify these issues ourselves. I will change my code to use labsdb1001 for now.
Thanks, Huji On Sun, Aug 30, 2015 at 1:27 PM, Jaime Crespo <[email protected]> wrote: > On Sat, Aug 29, 2015 at 10:36 PM, Alex Monk <[email protected]> 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. > > > _______________________________________________ > Labs-l mailing list > [email protected] > https://lists.wikimedia.org/mailman/listinfo/labs-l > >
_______________________________________________ Labs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/labs-l
