Oh that is smart! By the way, after some exploration I figured out I really don't know how to connect to a specific labsdb instance. What is the host name I should be using on command line? fawiki.labsdb001 is not the answer.
On Sun, Aug 30, 2015 at 5:33 PM, Bruce Myers <[email protected]> wrote: > Here is an EXPLAIN work around that I learned from examining the code of > the inoperable Query Analyzer tool > https://tools.wmflabs.org/tools-info/optimizer.py > > Open 2 SQL sessions > > In session 1: > SELECT CONNECTION_ID() AS conid; > Note the number returned. > Run the query to be explained. > > In session 2: > Use the number noted above for <conid> > SHOW EXPLAIN FOR <conid>; > > SHOW EXPLAIN is a MariaDB extension usable by regular users. > > > On 15-08-30 05:01 PM, Huji Lee wrote: > > 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]> >> [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 > [email protected]https://lists.wikimedia.org/mailman/listinfo/labs-l > > > > _______________________________________________ > 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
