<div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif">With two connections to the DB via Terminal, it is possible! Here is what I got; it matches what you sent above too:<br><br><br><br><font size="1"><span style="font-family:monospace,monospace">MariaDB [fawiki_p]> show processlist;<br>+---------+--------+-------------------+----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+----------+<br>| Id | User | Host | db | Command | Time | State | Info | Progress |<br>+---------+--------+-------------------+----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+----------+<br>| 3556437 | s51403 | <a href="http://10.68.23.58:53391">10.68.23.58:53391</a> | fawiki_p | Query | 19 | Queried about 5450000 rows | select page_title, count(ll_lang) from page join category on page_title = cat_title left join catego | 0.000 |<br>| 3556865 | s51403 | <a href="http://10.68.23.58:54083">10.68.23.58:54083</a> | fawiki_p | Query | 0 | init | show processlist | 0.000 |<br>+---------+--------+-------------------+----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+----------+<br>2 rows in set (0.00 sec)<br><br>MariaDB [fawiki_p]> show explain for 3556437;<br>+------+-------------+---------------+--------+----------------------------------------+-----------------------------+---------+------------------------+--------+----------------------------------------------+<br>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br>+------+-------------+---------------+--------+----------------------------------------+-----------------------------+---------+------------------------+--------+----------------------------------------------+<br>| 1 | SIMPLE | page | ref | name_title,page_redirect_namespace_len | page_redirect_namespace_len | 5 | const,const | 364177 | Using where; Using temporary; Using filesort |<br>| 1 | SIMPLE | category | eq_ref | cat_title | cat_title | 257 | fawiki.page.page_title | 1 | Using index |<br>| 1 | SIMPLE | categorylinks | ref | cl_timestamp,cl_sortkey | cl_timestamp | 257 | fawiki.page.page_title | 12 | Using where; Using index |<br>| 1 | SIMPLE | templatelinks | ref | tl_from,tl_namespace | tl_namespace | 4 | const | 390622 | Using where; Using index |<br>| 1 | SIMPLE | langlinks | ref | ll_from | ll_from | 4 | fawiki.page.page_id | 104911 | Using index |<br>+------+-------------+---------------+--------+----------------------------------------+-----------------------------+---------+------------------------+--------+----------------------------------------------+<br></span></font><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 23, 2016 at 11:58 AM, Huji Lee <span dir="ltr"><<a href="mailto:huji.huji@gmail.com" target="_blank">huji.huji@gmail.com</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"><div class="gmail_default" style="font-family:tahoma,sans-serif">Oh, I misread your comment.<br><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">I doubt SHOW EXPLAIN would work via Quarry, so I am trying to get it to work via terminal access to Labs. I wonder though if it works for a query that fails to complete.<br></div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 23, 2016 at 11:53 AM, Jaime Crespo <span dir="ltr"><<a 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">Huji, note I suggested SHOW EXPLAIN, not EXPLAIN; it is a different command, check the link I provided and report if that doesn't work for you.<br></div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 23, 2016 at 5:31 PM, Huji Lee <span dir="ltr"><<a href="mailto:huji.huji@gmail.com" target="_blank">huji.huji@gmail.com</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"><div style="font-family:tahoma,sans-serif" class="gmail_default">See <a href="https://quarry.wmflabs.org/query/12718" target="_blank">https://quarry.wmflabs.org/que<wbr>ry/12718</a> and the error message returned.<br><br></div><div style="font-family:tahoma,sans-serif" class="gmail_default">EXPLAIN only works if you have access to the underlying tables. I only have access to the VIEWs built on top of actual wiki tables (for good reason, same applies to most of us) so I get an error when I try EXPLAIN.<br></div></div><div><div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Sep 23, 2016 at 11:24 AM, Jaime Crespo <span dir="ltr"><<a 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">Explain (you should be able to run SHOW EXPLAIN on your own queries: <a href="http://s.petrunia.net/blog/?p=89" target="_blank">http://s.petrunia.net/blog/?p=<wbr>89</a>):<br><br>EXPLAIN select page_title, count(ll_lang) 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 ( 'رده_خالی' , 'رده_بهتر', 'رده_ابهامزدایی', 'رده_ردیابیکردن' ) left join langlinks on page_id = ll_from where page_namespace = 14 and page_is_redirect = 0 and cl_to is null and tl_title is null group by page_title order by 2, 1 limit 5000\G<br>*************************** 1. row ***************************<br> id: 1<br> select_type: SIMPLE<br> table: page<br> type: ref<br>possible_keys: name_title,page_redirect_names<wbr>pace_len<br> key: page_redirect_namespace_len<br> key_len: 5<br> ref: const,const<br> rows: 364273<br> Extra: Using where; Using temporary; Using filesort<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: 12<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<br> key: tl_namespace<br> key_len: 4<br> ref: const<br> rows: 390610<br> Extra: Using where; Using index<br>*************************** 5. row ***************************<br> id: 1<br> select_type: SIMPLE<br> table: langlinks<br> type: ref<br>possible_keys: ll_from<br> key: ll_from<br> key_len: 4<br> ref: fawiki.page.page_id<br> rows: 104910<br> Extra: Using index<br></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>On Fri, Sep 23, 2016 at 5:16 PM, Huji Lee <span dir="ltr"><<a href="mailto:huji.huji@gmail.com" target="_blank">huji.huji@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div dir="ltr"><div class="gmail_default" style="font-family:tahoma,sans-serif">Hi all,<br><br>I have a query [1] which I run weekly to identify empty categories; those that don't have interwiki links and stay empty for a while are then deleted by a sysop.<br><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">The query ran just fine every week until Sep 2nd. [2] Since then, the query times out and the page doesn't get updated.<br><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">The query is no that complex (a SELECT with five JOINs) and used to finish in about 3-5 minutes. I don't have EXPLAIN access on Labs so I cannot tell what is slowing it down. Can someone kindly take a look and advise why this suddenly stopped working?<br><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">Thanks,<br><br></div><div class="gmail_default" style="font-family:tahoma,sans-serif">Huji<br></div><div class="gmail_default" style="font-family:tahoma,sans-serif"><br>[1] <a href="https://quarry.wmflabs.org/query/3760" target="_blank">https://quarry.wmflabs.org/que<wbr>ry/3760</a><br>[2] <a href="https://fa.wikipedia.org/w/index.php?title=%D9%88%DB%8C%DA%A9%DB%8C%E2%80%8C%D9%BE%D8%AF%DB%8C%D8%A7:%DA%AF%D8%B2%D8%A7%D8%B1%D8%B4_%D8%AF%DB%8C%D8%AA%D8%A7%D8%A8%DB%8C%D8%B3/%D8%B1%D8%AF%D9%87%E2%80%8C%D9%87%D8%A7%DB%8C_%D8%AE%D8%A7%D9%84%DB%8C&action=history&uselang=en" target="_blank">https://fa.wikipedia.org/w/ind<wbr>ex.php?title=%D9%88%DB%8C%DA%A<wbr>9%DB%8C%E2%80%8C%D9%BE%D8%AF%D<wbr>B%8C%D8%A7:%DA%AF%D8%B2%D8%A7%<wbr>D8%B1%D8%B4_%D8%AF%DB%8C%D8%AA<wbr>%D8%A7%D8%A8%DB%8C%D8%B3/%D8%B<wbr>1%D8%AF%D9%87%E2%80%8C%D9%87%D<wbr>8%A7%DB%8C_%D8%AE%D8%A7%D9%84%<wbr>DB%8C&action=history&uselang=<wbr>en</a><br></div></div>
<br></div></div>______________________________<wbr>_________________<br>
Labs-l mailing list<br>
<a href="mailto:Labs-l@lists.wikimedia.org" target="_blank">Labs-l@lists.wikimedia.org</a><br>
<a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" rel="noreferrer" target="_blank">https://lists.wikimedia.org/ma<wbr>ilman/listinfo/labs-l</a><br>
<br></blockquote></div><span><font color="#888888"><br><br clear="all"><br>-- <br><div data-smartmail="gmail_signature"><div dir="ltr"><div>Jaime Crespo<br></div><<a href="http://wikimedia.org" target="_blank">http://wikimedia.org</a>><br></div></div>
</font></span></div>
<br>______________________________<wbr>_________________<br>
Labs-l mailing list<br>
<a href="mailto:Labs-l@lists.wikimedia.org" target="_blank">Labs-l@lists.wikimedia.org</a><br>
<a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" rel="noreferrer" target="_blank">https://lists.wikimedia.org/ma<wbr>ilman/listinfo/labs-l</a><br>
<br></blockquote></div><br></div>
</div></div><br>______________________________<wbr>_________________<br>
Labs-l mailing list<br>
<a href="mailto:Labs-l@lists.wikimedia.org" target="_blank">Labs-l@lists.wikimedia.org</a><br>
<a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" rel="noreferrer" target="_blank">https://lists.wikimedia.org/ma<wbr>ilman/listinfo/labs-l</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br><div data-smartmail="gmail_signature"><div dir="ltr"><div>Jaime Crespo<br></div><<a href="http://wikimedia.org" target="_blank">http://wikimedia.org</a>><br></div></div>
</div>
</div></div><br>______________________________<wbr>_________________<br>
Labs-l mailing list<br>
<a href="mailto:Labs-l@lists.wikimedia.org" target="_blank">Labs-l@lists.wikimedia.org</a><br>
<a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" rel="noreferrer" target="_blank">https://lists.wikimedia.org/ma<wbr>ilman/listinfo/labs-l</a><br>
<br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>