dewiki: Most blocked registered users with inf. block status

SQL

USE dewiki_p;

SELECT 
	@curRank := @curRank + 1 AS rank, 
    log_title, 
    number_of_blocks, 
    log_title IN (SELECT ipb_address FROM ipblocks WHERE ipb_user<>0 AND ipb_expiry='infinity') AS inf_blocked 
FROM
(SELECT 
	REPLACE(log_title, '_', ' ') as log_title,
    COUNT(log_title) AS number_of_blocks
FROM
	logging_userindex
WHERE
	log_namespace=2 AND
    log_type='block' AND
    log_action='block' AND -- but not 'reblock' or 'unblock'
    log_title NOT REGEXP '^[0-9]{1,3}(\.[0-9]{1,3}){0,3}(\/[0-9]{1,2})?$' AND -- no IPv4 addresses (and ranges)
    log_title NOT REGEXP '^[0-9A-Fa-f]{1,4}(:([0-9A-Fa-f]{1,4})?){1,7}(\/[0-9]{1,2})?$' -- no IPv6 addresses (and ranges)
GROUP BY
	log_title
HAVING
	COUNT(log_title)>=10 -- otherwise quickly inefficient
ORDER BY
	number_of_blocks DESC) AS blocks, 
(SELECT @curRank := 0) AS r;
SQL

Checking query status...
