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;
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?
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) bjorsch@wikimedia.org wrote:
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