<html><head></head><body><div style="color:#000; background-color:#fff; font-family:Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:13px"><div id="yui_3_16_0_ym19_1_1495967060591_5974"><span id="yui_3_16_0_ym19_1_1495967060591_5973">This one works better.</span></div><div id="yui_3_16_0_ym19_1_1495967060591_5975"><span><br></span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1495967060591_5959"><span id="yui_3_16_0_ym19_1_1495967060591_5958">use fawiki_p;<br id="yui_3_16_0_ym19_1_1495967060591_5931">select<br id="yui_3_16_0_ym19_1_1495967060591_5932"> max(l1.log_id), max(l1.log_timestamp), l1.log_title,<br id="yui_3_16_0_ym19_1_1495967060591_5933">max(l2.log_id), max(l2.log_timestamp), l2.log_user<br id="yui_3_16_0_ym19_1_1495967060591_5934"><br id="yui_3_16_0_ym19_1_1495967060591_5935">from<br id="yui_3_16_0_ym19_1_1495967060591_5936">(<br id="yui_3_16_0_ym19_1_1495967060591_5937">select log_id, log_timestamp, log_title<br id="yui_3_16_0_ym19_1_1495967060591_5938">from logging_userindex<br id="yui_3_16_0_ym19_1_1495967060591_5939">where log_type = 'block'<br id="yui_3_16_0_ym19_1_1495967060591_5940">and log_action = 'block'<br id="yui_3_16_0_ym19_1_1495967060591_5941">and log_user = 427882 -- User:Abuse filter<br id="yui_3_16_0_ym19_1_1495967060591_5942">order by log_id desc<br id="yui_3_16_0_ym19_1_1495967060591_5943">limit 100<br id="yui_3_16_0_ym19_1_1495967060591_5944">) l1<br id="yui_3_16_0_ym19_1_1495967060591_5945">inner join logging_logindex l2<br id="yui_3_16_0_ym19_1_1495967060591_5946"> on l2.log_type = 'block'<br id="yui_3_16_0_ym19_1_1495967060591_5947"> and l2.log_action = 'unblock'<br id="yui_3_16_0_ym19_1_1495967060591_5948"> and l2.log_title = l1.log_title -- same target account<br id="yui_3_16_0_ym19_1_1495967060591_5949"> and l2.log_namespace = 2 -- to allow using the page_time index<br id="yui_3_16_0_ym19_1_1495967060591_5950"><br id="yui_3_16_0_ym19_1_1495967060591_5951">where<br id="yui_3_16_0_ym19_1_1495967060591_5952"> l2.log_timestamp > l1.log_timestamp<br id="yui_3_16_0_ym19_1_1495967060591_5953"><br id="yui_3_16_0_ym19_1_1495967060591_5954">group by<br id="yui_3_16_0_ym19_1_1495967060591_5955"><br id="yui_3_16_0_ym19_1_1495967060591_5956">l1.log_title, l2.log_user<br id="yui_3_16_0_ym19_1_1495967060591_5957"></span></div> <div class="qtdSeparateBR"><br><br></div><div class="yahoo_quoted" style="display: block;"> <div style="font-family: Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 13px;"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div dir="ltr"><font size="2" face="Arial"> On Sunday, May 28, 2017 10:04 AM, Huji Lee <huji.huji@gmail.com> wrote:<br></font></div> <br><br> <div class="y_msg_container"><div id="yiv6948898199"><div dir="ltr"><div class="yiv6948898199gmail_default" style="font-family:tahoma, sans-serif;">Hi,<br><br></div><div class="yiv6948898199gmail_default" style="font-family:tahoma, sans-serif;">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.<br><br></div><div class="yiv6948898199gmail_default" style="font-family:tahoma, sans-serif;">My attempts so far have lead to <a rel="nofollow" target="_blank" href="https://quarry.wmflabs.org/query/15158">https://quarry.wmflabs.org/query/15158</a> 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.<br><br></div><div class="yiv6948898199gmail_default" style="font-family:tahoma, sans-serif;">Does anyone have a better idea on how to optimize this query?<br><br></div><div class="yiv6948898199gmail_default" style="font-family:tahoma, sans-serif;">Thanks,<br><br></div><div class="yiv6948898199gmail_default" style="font-family:tahoma, sans-serif;">Huji<br></div></div></div>_______________________________________________<br>Labs-l mailing list<br><a ymailto="mailto:Labs-l@lists.wikimedia.org" href="mailto:Labs-l@lists.wikimedia.org">Labs-l@lists.wikimedia.org</a><br><a href="https://lists.wikimedia.org/mailman/listinfo/labs-l" target="_blank">https://lists.wikimedia.org/mailman/listinfo/labs-l</a><br><br><br></div> </div> </div> </div></div></body></html>