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]
<mailto:[email protected]>> wrote:
On Sat, Aug 29, 2015 at 10:36 PM, Alex Monk <[email protected]
<mailto:[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] <mailto:[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
