Comparison of SQL Approaches for Counting Threads

Comparison.txt

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)