[Labs-l] Optimizing a query

Sun May 28 13:20:37 UTC 2017

This one works better.
use fawiki_p;
select
    max(l1.log_id), max(l1.log_timestamp), l1.log_title,
max(l2.log_id), max(l2.log_timestamp), l2.log_user

from
(
select log_id, log_timestamp, log_title
from logging_userindex
where log_type = 'block'
and log_action = 'block'
and log_user = 427882 -- User:Abuse filter
order by log_id desc
limit 100
) l1
inner join logging_logindex l2
  on l2.log_type = 'block'
  and l2.log_action = 'unblock'
  and l2.log_title = l1.log_title -- same target account
  and l2.log_namespace = 2 -- to allow using the page_time index

where
    l2.log_timestamp > l1.log_timestamp

group by

l1.log_title, l2.log_user

    On Sunday, May 28, 2017 10:04 AM, Huji Lee <huji.huji at gmail.com> wrote:

 Hi,

I am trying to optimize a query which aims to do this: find the latest 100 blocks performed by a certain user (in this case, the Abuse filter account), and figure out if any of them were later unblocked by any user.

My attempts so far have lead to https://quarry.wmflabs.org/query/15158 which is still not efficient. I tried including the log_namespace in the join conditions to encourage the use of page_time index, but that wasn't helpful.

Does anyone have a better idea on how to optimize this query?

Thanks,

Huji
_______________________________________________
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/20170528/baf02a7c/attachment.html>