On Sun, Aug 18, 2019 at 11:55 AM John <phoenixoverride(a)gmail.com> wrote:
I an updating a tool I missed in the initial rounds of
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
select log_timestamp, actor_name, log_action, log_title, comment_text,
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