Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Comparison of SQL Approaches for Counting Threads
Option A: Two Subqueries
SELECT
`Thread-Category`.`value` as category_id,
COALESCE((SELECT unread FROM `Thread-Counts` WHERE category_id = `Thread-Category`.`value`), 0) + SUM(unread) as unread,
COALESCE((SELECT total FROM `Thread-Counts` WHERE category_id = `Thread-Category`.`value`), 0) + COUNT(*) as total
FROM `Thread`
INNER JOIN `Thread-Category` ON `Thread`.`id` = `Thread-Category`.`id`
WHERE
`Thread`.in_all_mail = 1
GROUP BY `Thread-Category`.`value`
SCAN TABLE Thread-Category USING COVERING INDEX Thread_Category_val_id (~1000000 rows)
SEARCH TABLE Thread USING INDEX Thread_id (id=?) (~1 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE Thread-Counts USING INDEX ThreadCountsIndex (category_id=?) (~1 rows)
EXECUTE CORRELATED SCALAR SUBQUERY 2
SEARCH TABLE Thread-Counts USING INDEX ThreadCountsIndex (category_id=?) (~1 rows)
(94 record explain)
(0.17 - 0.19s) - 10% faster!
Option B: Left join
SELECT `Thread-Category`.`value`, IFNULL(`Thread-Counts`.`unread`, 0) + SUM(`Thread`.`unread`) as unread, IFNULL(`Thread-Counts`.`total`, 0) + COUNT(*) as total
FROM `Thread`
INNER JOIN `Thread-Category` ON `Thread`.`id` = `Thread-Category`.`id`
LEFT JOIN `Thread-Counts` ON `Thread-Counts`.`category_id` = `Thread-Category`.`value`
WHERE
`Thread`.in_all_mail = 1
GROUP BY `Thread-Category`.`value`
SCAN TABLE Thread-Category USING COVERING INDEX Thread_Category_val_id (~1000000 rows)
SEARCH TABLE Thread USING INDEX Thread_id (id=?) (~1 rows)
SEARCH TABLE Thread-Counts USING INDEX ThreadCountsIndex (category_id=?) (~1 rows)
(86 record explain)
(0.21 - 0.22 seconds)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment