[Labs-l] Query optimization

[John]

I don't have it handy but category membership counts are stored in the db
use that instead of a count()

On Tuesday, March 8, 2016, Huji Lee <huji.huji at gmail.com> wrote:

> I am trying to find a list of those categories on EN WP that are highly
> used (more than 100 subpages or subcats) but don't have a counterpart in FA
> WP. The query I used is shown below and also on
> https://quarry.wmflabs.org/query/7943 and is extremely slow.
>
> Any thoughts on how to make it more efficient?
>
> select page_title, ll_title, count(cl_to) as CNT
> from page
> left join langlinks
> on ll_from = page_id
> and ll_lang = 'fa'
> join categorylinks
> on page_title = cl_to
> where page_namespace = 14
> and ll_from is null
> group by cl_to, ll_title
> having CNT > 100
> order by CNT desc;
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20160308/8eb403fa/attachment.html>