[Labs-l] Empty Cat query

Fri Sep 23 15:24:07 UTC 2016

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%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
>
> _______________________________________________
> Labs-l mailing list
> Labs-l at lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/labs-l
>
>

-- 
Jaime Crespo
<http://wikimedia.org>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20160923/9c10fc4e/attachment.html>