the original query is select log_timestamp, user_name, log_action, log_title, log_comment, log_params from logging_userindex left join user on user_id = log_user where log_type = 'block' and log_namespace = 2 and log_title like 'Prefix%' order by log_timestamp;
and 'Prefix%' is correct, that was a sloppy copy/paste. On Mon, Aug 19, 2019 at 11:50 AM Brad Jorsch (Anomie) <[email protected]> wrote: > On Sun, Aug 18, 2019 at 11:55 AM John <[email protected]> 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 > [email protected] (formerly [email protected]) > https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________ Wikimedia Cloud Services mailing list [email protected] (formerly [email protected]) https://lists.wikimedia.org/mailman/listinfo/cloud
