<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Here is an EXPLAIN work around that I
learned from examining the code of the inoperable Query Analyzer
tool <a class="moz-txt-link-freetext" href="https://tools.wmflabs.org/tools-info/optimizer.py">https://tools.wmflabs.org/tools-info/optimizer.py</a><br>
<br>
Open 2 SQL sessions<br>
<br>
In session 1:<br>
SELECT CONNECTION_ID() AS conid;<br>
Note the number returned.<br>
Run the query to be explained.<br>
<br>
In session 2:<br>
Use the number noted above for <conid><br>
SHOW EXPLAIN FOR <conid>;<br>
<br>
SHOW EXPLAIN is a MariaDB extension usable by regular users.<br>
<br>
On 15-08-30 05:01 PM, Huji Lee wrote:<br>
</div>
<blockquote
cite="mid:CALYfd===NUWNzthAoaVJTiF5miqbfUHNhwMbT-Lm+wv26CWnQQ@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>
<div>
<div>That explains a lot.<br>
<br>
</div>
Unfortunately, we (regular uses of Labs) cannot run EXPLAIN
to identify these issues ourselves. I will change my code to
use labsdb1001 for now.<br>
<br>
</div>
Thanks,<br>
<br>
</div>
Huji<br>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Sun, Aug 30, 2015 at 1:27 PM, Jaime
Crespo <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:jcrespo@wikimedia.org" target="_blank">jcrespo@wikimedia.org</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr"><span class="">On Sat, Aug 29, 2015 at 10:36
PM, Alex Monk <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:krenair@gmail.com" target="_blank"><a class="moz-txt-link-abbreviated" href="mailto:krenair@gmail.com">krenair@gmail.com</a></a>></span>
wrote:<br>
</span>
<div class="gmail_extra">
<div class="gmail_quote"><span class="">
<blockquote class="gmail_quote" style="margin:0px
0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">
<div dir="ltr">Looks like it works on labsdb1001,
but not labsdb1002 and labsdb1003.</div>
</blockquote>
<div><br>
</div>
</span>
<div>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":<br>
<br>
MariaDB LABS labsdb1002 fawiki_p > EXPLAIN select
page_title<br>
-> from page<br>
-> join category on page_title = cat_title<br>
-> left join categorylinks on page_title =
cl_to<br>
-> left join templatelinks on tl_from =
page_id and tl_namespace = 10 and tl_title in
('رده_خالی' , 'رده_بهتر')<br>
-> where page_namespace = 14 and cl_to is
null and tl_title is null<br>
-> group by page_title\G<br>
*************************** 1. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: page<br>
type: ref<br>
possible_keys: name_title<br>
key: name_title<br>
key_len: 4<br>
ref: const<br>
rows: 322989<br>
Extra: Using where; Using index<br>
*************************** 2. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: category<br>
type: eq_ref<br>
possible_keys: cat_title<br>
key: cat_title<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 1<br>
Extra: Using index<br>
*************************** 3. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: categorylinks<br>
type: ref<br>
possible_keys: cl_timestamp,cl_sortkey<br>
key: cl_timestamp<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 72785<br>
Extra: Using where; Using index<br>
*************************** 4. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: templatelinks<br>
type: ref<br>
possible_keys:
tl_from,tl_namespace,tl_backlinks_namespace<br>
key: tl_namespace<br>
key_len: 4<br>
ref: const<br>
rows: 383047<br>
Extra: Using where; Using index<br>
4 rows in set (0.01 sec)<br>
<br>
<br>
<br>
<br>
MariaDB LABS labsdb1001 fawiki_p > EXPLAIN select
page_title<br>
-> from page<br>
-> join category on page_title = cat_title<br>
-> left join categorylinks on page_title =
cl_to<br>
-> left join templatelinks on tl_from =
page_id and tl_namespace = 10 and tl_title in
('رده_خالی' , 'رده_بهتر')<br>
-> where page_namespace = 14 and cl_to is
null and tl_title is null<br>
-> group by page_title\G<br>
*************************** 1. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: page<br>
type: ref<br>
possible_keys: name_title<br>
key: name_title<br>
key_len: 4<br>
ref: const<br>
rows: 340784<br>
Extra: Using where; Using index<br>
*************************** 2. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: category<br>
type: eq_ref<br>
possible_keys: cat_title<br>
key: cat_title<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 1<br>
Extra: Using index<br>
*************************** 3. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: categorylinks<br>
type: ref<br>
possible_keys: cl_timestamp,cl_sortkey<br>
key: cl_timestamp<br>
key_len: 257<br>
ref: fawiki.page.page_title<br>
rows: 10<br>
Extra: Using where; Using index<br>
*************************** 4. row
***************************<br>
id: 1<br>
select_type: SIMPLE<br>
table: templatelinks<br>
type: ref<br>
possible_keys:
tl_from,tl_namespace,tl_backlinks_namespace<br>
key: tl_from<br>
key_len: 8<br>
ref: fawiki.page.page_id,const<br>
rows: 16<br>
Extra: Using where; Using index<br>
4 rows in set (0.00 sec)<br>
<br>
</div>
<div>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.<br>
<br>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Labs-l mailing list<br>
<a moz-do-not-send="true"
href="mailto:Labs-l@lists.wikimedia.org">Labs-l@lists.wikimedia.org</a><br>
<a moz-do-not-send="true"
href="https://lists.wikimedia.org/mailman/listinfo/labs-l"
rel="noreferrer" target="_blank">https://lists.wikimedia.org/mailman/listinfo/labs-l</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Labs-l mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Labs-l@lists.wikimedia.org">Labs-l@lists.wikimedia.org</a>
<a class="moz-txt-link-freetext" href="https://lists.wikimedia.org/mailman/listinfo/labs-l">https://lists.wikimedia.org/mailman/listinfo/labs-l</a>
</pre>
</blockquote>
<br>
</body>
</html>