The query took more than an hour, so I killed it.
I think a better strategy is to first run the query on logging, and get a list of logs, then run the subquery for retrieving the edit counts.
My new query is shown below; however, I cannot create temporary tables with my user on Toolforge. I tried creating a database on "tools" server, but then i cannot access it from the server that hosts fawiki_p
What is the proper strategy for temporary (physical or in-memory) tables on Toolforge?
Huji
create temporary table logs
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
from logging_userindex
join page
on page_id = log_page
left join user_groups
on log_user = ug_user
and ug_group = 'bot'
where log_type = 'move'
and log_id > 9406768;
select
logs.*,
(
select count(1)
from revision_userindex
where
rev_user = log_user
and rev_timestamp < log_timestamp
) as rev_count_before_move
from logs;