On Sun, Aug 18, 2019 at 11:55 AM John <phoenixoverride@gmail.com> wrote:

I an updating a tool I missed in the initial rounds of the actor/comment table changes. This query used to run in ~30 seconds or so. Now its at a staggering 10 minute run time. Anyone able to lend a hand on getting this optimized?

select log_timestamp, actor_name, log_action, log_title, comment_text, log_params
from logging_userindex
left join  actor_logging on actor_id = log_actor
left join comment_logging on comment_id = log_comment_id
where log_type = 'block' and log_namespace = 2 and
log_title like '%s%%'
order by log_timestamp;

Is the "%s%%" supposed to be a printf() code, so the actual query looks like "log_title like 'Prefix%'"? In that case you should use logging_logindex instead of logging_userindex so as to take advantage of the index on (log_namespace, log_title, log_timestamp). If possible you might also change it to "order by log_title, log_timestamp" to make it even faster, although I suspect that won't fit with whatever you're trying to do.

What was the original query before you tried to add actor and comment table stuff to it?

--

Brad Jorsch (Anomie)
Senior Software Engineer
Wikimedia Foundation

_______________________________________________
Wikimedia Cloud Services mailing list
Cloud@lists.wikimedia.org (formerly labs-l@lists.wikimedia.org)
https://lists.wikimedia.org/mailman/listinfo/cloud