[Labs-l] Empty Cat query

[Huji Lee]

Oh, I misread your comment.

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.

On Fri, Sep 23, 2016 at 11:53 AM, Jaime Crespo <jcrespo at wikimedia.org>
wrote:

> 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.
>
> On Fri, Sep 23, 2016 at 5:31 PM, Huji Lee <huji.huji at gmail.com> wrote:
>
>> See https://quarry.wmflabs.org/query/12718 and the error message
>> returned.
>>
>> 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.
>>
>> On Fri, Sep 23, 2016 at 11:24 AM, Jaime Crespo <jcrespo at wikimedia.org>
>> wrote:
>>
>>> Explain (you should be able to run SHOW EXPLAIN on your own queries:
>>> http://s.petrunia.net/blog/?p=89):
>>>
>>> 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
>>> *************************** 1. row ***************************
>>>            id: 1
>>>   select_type: SIMPLE
>>>         table: page
>>>          type: ref
>>> possible_keys: name_title,page_redirect_namespace_len
>>>           key: page_redirect_namespace_len
>>>       key_len: 5
>>>           ref: const,const
>>>          rows: 364273
>>>         Extra: Using where; Using temporary; Using filesort
>>> *************************** 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: 12
>>>         Extra: Using where; Using index
>>> *************************** 4. row ***************************
>>>            id: 1
>>>   select_type: SIMPLE
>>>         table: templatelinks
>>>          type: ref
>>> possible_keys: tl_from,tl_namespace
>>>           key: tl_namespace
>>>       key_len: 4
>>>           ref: const
>>>          rows: 390610
>>>         Extra: Using where; Using index
>>> *************************** 5. row ***************************
>>>            id: 1
>>>   select_type: SIMPLE
>>>         table: langlinks
>>>          type: ref
>>> possible_keys: ll_from
>>>           key: ll_from
>>>       key_len: 4
>>>           ref: fawiki.page.page_id
>>>          rows: 104910
>>>         Extra: Using index
>>>
>>> On Fri, Sep 23, 2016 at 5:16 PM, Huji Lee <huji.huji at gmail.com> wrote:
>>>
>>>> Hi all,
>>>>
>>>> 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.
>>>>
>>>> The query ran just fine every week until Sep 2nd. [2] Since then, the
>>>> query times out and the page doesn't get updated.
>>>>
>>>> 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?
>>>>
>>>> Thanks,
>>>>
>>>> Huji
>>>>
>>>> [1] https://quarry.wmflabs.org/query/3760
>>>> [2] https://fa.wikipedia.org/w/index.php?title=%D9%88%DB%8C%DA%A
>>>> 9%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
>>>>
>>>> _______________________________________________
>>>> Labs-l mailing list
>>>> Labs-l at lists.wikimedia.org
>>>> https://lists.wikimedia.org/mailman/listinfo/labs-l
>>>>
>>>>
>>>
>>>
>>> --
>>> Jaime Crespo
>>> <http://wikimedia.org>
>>>
>>> _______________________________________________
>>> Labs-l mailing list
>>> Labs-l at lists.wikimedia.org
>>> https://lists.wikimedia.org/mailman/listinfo/labs-l
>>>
>>>
>>
>> _______________________________________________
>> Labs-l mailing list
>> Labs-l at lists.wikimedia.org
>> https://lists.wikimedia.org/mailman/listinfo/labs-l
>>
>>
>
>
> --
> Jaime Crespo
> <http://wikimedia.org>
>
> _______________________________________________
> Labs-l mailing list
> Labs-l at lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/labs-l
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20160923/4936dd42/attachment.html>