Articles written by a single editor (tests)

SQL

USE enwiki_p;

SELECT page_title, page_len, COUNT(rev_actor) FROM
(
SELECT DISTINCT p.page_title, p.page_len, r.rev_actor, a.actor_name #COUNT(r.rev_actor) does not work because of the DISTINCT
FROM
  (
    SELECT page_title, page_len, page_id
   FROM page
   WHERE page_namespace = 0 # Mainspace
    AND NOT page_is_redirect
   ORDER BY page_len DESC
   LIMIT 10000 
  ) AS p
LEFT JOIN revision_userindex r
ON r.rev_page = p.page_id
LEFT JOIN actor a
ON r.rev_actor = a.actor_id
WHERE NOT IS_IPV4(a.actor_name)
AND NOT IS_IPV6(a.actor_name)
AND LOWER(a.actor_name) NOT LIKE '%script%'
AND LOWER(a.actor_name) NOT LIKE '%bot%' # TODO LOWER does not seem to work, I see InternetArchiveBot and AnomieBOT passing.
AND a.actor_name NOT LIKE '%Bot%'
AND a.actor_name NOT LIKE '%BOT%'
#LIMIT 250000 # Prevent the query from taking too much time. 100000:10minutes 250000:just below the 30min timeout threshhold
) AS myalias
GROUP BY (page_title)
HAVING COUNT(rev_actor) < 2
ORDER BY page_len DESC # Short article are usually disambiguation pages, with low chances of requiring attention.
SQL

Checking query status...
