Use the logging_userindex table instead of logging On Sat, Dec 30, 2017 at 12:01 PM Huji Lee <huji.h...@gmail.com> wrote:
> I wrote this query to find all page moves done in fawiki in 2017, and > determine how many edits the performing user had prior to that page move. > The query tries to use indexes, as much as I could think of, and yet it > runs for a very long time (more than 20 min, at which point it gets killed). > > Any ideas on how to further optimize this query is appreciated! > > Thanks, > Huji > > > use fawiki_p; > select > log_id, > log_timestamp, > log_user, > log_user_text, > log_title, > log_comment, > log_page, > page_namespace, > case when ug_group = 'bot' then 1 else 0 end as user_is_bot, > ( > select count(*) > from revision_userindex > where > rev_user = log_user > and rev_timestamp < log_timestamp > ) as rev_count_before_move > from logging > join page > on page_id = log_page > left join user_groups > on log_user = ug_user > and ug_group = 'bot' > where log_action = 'move' > and log_timestamp > '20170101000000' > _______________________________________________ > Wikimedia Cloud Services mailing list > Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) > https://lists.wikimedia.org/mailman/listinfo/cloud
_______________________________________________ Wikimedia Cloud Services mailing list Cloud@lists.wikimedia.org (formerly lab...@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud
