Right @asmecher ! The query log when execute
# User@Host: root[root] @ localhost [] Id: 624
# Query_time: 135.534792 Lock_time: 0.000519 Rows_sent: 20 Rows_examined: 22943974
SET timestamp=1496186198;
SELECT u.*,
COUNT(DISTINCT rac.review_id) AS complete_count,
AVG(DATEDIFF(rac.date_completed, rac.date_notified)) AS average_time,
MAX(raf.date_assigned) AS last_assigned,
COUNT(DISTINCT rai.review_id) AS incomplete_count
FROM users u
JOIN user_user_groups uug ON (uug.user_id = u.user_id)
JOIN user_groups ug ON (ug.user_group_id = uug.user_group_id AND ug.role_id = 4096 AND ug.context_id = 3)
JOIN user_group_stage ugs ON (ugs.user_group_id = ug.user_group_id AND ugs.stage_id = 3)
LEFT JOIN review_assignments ras ON (ras.submission_id = 20770 AND ras.stage_id = 3 AND ras.review_round_id = 16559 AND ras.reviewer_id=u.user_id)
LEFT JOIN review_assignments rac ON (rac.reviewer_id = u.user_id AND rac.date_notified IS NOT NULL AND rac.date_completed IS NOT NULL)
LEFT JOIN review_assignments raf ON (raf.reviewer_id = u.user_id)
LEFT JOIN review_assignments ran ON (ran.reviewer_id = u.user_id AND ran.review_id > raf.review_id)
LEFT JOIN review_assignments rai ON (rai.reviewer_id = u.user_id AND rai.date_notified IS NOT NULL AND rai.date_completed IS NULL AND rai.cancelled = 0 AND rai.declined = 0 AND rai.replaced = 0)
WHERE ras.review_id IS NULL
AND ran.review_id IS NULL
GROUP BY u.user_id
HAVING 1=1 ORDER BY last_name, first_name LIMIT 0,20;
# Time: 170530 20:16:40
# User@Host: root[root] @ localhost [] Id: 626
# Query_time: 0.000497 Lock_time: 0.000247 Rows_sent: 1 Rows_examined: 1
SET timestamp=1496186200;
SELECT * FROM site;
# User@Host: root[root] @ localhost [] Id: 626
# Query_time: 0.003760 Lock_time: 0.001865 Rows_sent: 83 Rows_examined: 339
SET timestamp=1496186200;
SELECT v.*
FROM versions v LEFT JOIN plugin_settings ps ON
lower(v.product_class_name) = ps.plugin_name
AND ps.setting_name = 'enabled' AND (context_id = '3' OR v.sitewide = 1)
WHERE v.current = 1 AND (ps.setting_value = '1' OR v.lazy_load <> 1);
# Time: 170530 20:16:41
# User@Host: root[root] @ localhost [] Id: 626
# Query_time: 0.468306 Lock_time: 0.000211 Rows_sent: 1 Rows_examined: 727112
SET timestamp=1496186201;
SELECT count(*) FROM notifications WHERE user_id = 1 AND date_read IS NULL AND level = 3;
# Time: 170530 20:16:42
# User@Host: root[root] @ localhost [] Id: 626
# Query_time: 0.998741 Lock_time: 0.000116 Rows_sent: 0 Rows_examined: 727112
SET timestamp=1496186202;
SELECT * FROM notifications WHERE user_id = 1 AND level = 1 ORDER BY date_created DESC;
# User@Host: root[root] @ localhost [] Id: 626
# Query_time: 0.493938 Lock_time: 0.000092 Rows_sent: 0 Rows_examined: 727112
SET timestamp=1496186202;
SELECT * FROM notifications WHERE user_id = 1 AND level = 1 ORDER BY date_created DESC;