Slow count query with large data in MySQL

Question

I have three tables

points

id
name

products

id
name
point_id

verifies

id
name
product_id
result

When I create product, I need to give it a point, and after I verify a product, I will save the result in verifies table, now my client want to run points to count each different verify result of point, that's how I do.

SELECT `p`.`name`,
(SELECT COUNT(`pr`.`id`) FROM `products` `pr` INNER JOIN `verifies` `v` ON `pr`.`id' = `v`.`product_id` WHERE `pr`.`point_id` = `p`.`p_id` AND `v`.`result` = 1) AS `verify_result1`,
(SELECT COUNT(`pr`.`id`) FROM `products` `pr` INNER JOIN `verifies` `v` ON `pr`.`id' = `v`.`product_id` WHERE `pr`.`point_id` = `p`.`p_id` AND `v`.`result` = 2) AS `verify_result2`,
(SELECT COUNT(`pr`.`id`) FROM `products` `pr` INNER JOIN `verifies` `v` ON `pr`.`id' = `v`.`product_id` WHERE `pr`.`point_id` = `p`.`p_id` AND `v`.`result` = 3) AS `verify_result3`,
(SELECT COUNT(`pr`.`id`) FROM `products` `pr` INNER JOIN `verifies` `v` ON `pr`.`id' = `v`.`product_id` WHERE `pr`.`point_id` = `p`.`p_id` AND `v`.`result` = 4) AS `verify_result4`,
(SELECT COUNT(`pr`.`id`) FROM `products` `pr` INNER JOIN `verifies` `v` ON `pr`.`id' = `v`.`product_id` WHERE `pr`.`point_id` = `p`.`p_id` AND `v`.`result` = 5) AS `verify_result5`
FROM `points` `p`

The result is correct, but it's very slow because points table has over 3000 data, how to make it faster?

You can wrap this into one query, see this SO answer for "[count by value](http://stackoverflow.com/a/7367302/772086)" — Mike, Aug 16 '14 at 05:35

score 1 · Accepted Answer · answered Aug 16 '14 at 05:41

1

SELECT `p`.`name`,
  COUNT( IF( `v`.`result` = 1, 1, NULL ) ) As Result1,
  COUNT( IF( `v`.`result` = 2, 1, NULL ) ) As Result2,
  COUNT( IF( `v`.`result` = 3, 1, NULL ) ) As Result3,
  COUNT( IF( `v`.`result` = 4, 1, NULL ) ) As Result4,
  COUNT( IF( `v`.`result` = 5, 1, NULL ) ) As Result5,
FROM `points` `p`
  INNER JOIN `products` `pr` ON `p`.`p_id` = `pr`.`point_id`
  INNER JOIN `verifies` `v` ON `pr`.`id' = `v`.`product_id`
GROUP BY `p`.`name`

answered Aug 16 '14 at 05:41

Mike

3,641
3
29
39

If verifies table have more condition like type = 2, how to do it? – Chan Aug 16 '14 at 06:18
You want the first parameter of `IF()` to resolve to `True`, so instead of `result = 1`, use `result = 1 AND type = 2`. See a similar example with [this SO answer](http://stackoverflow.com/a/9112102). – Mike Aug 16 '14 at 23:22

score 0 · Answer 2 · answered Aug 16 '14 at 05:47

You could use SUM also instead of COUNT like:

SELECT `p`.`name`
        , SUM(IF(result=1,1,0)) as verify_result1
        , SUM(IF(result=2,1,0)) as verify_result2
        , SUM(IF(result=3,1,0)) as verify_result3
        , SUM(IF(result=4,1,0)) as verify_result4
        , SUM(IF(result=5,1,0)) as verify_result5
FROM points p
INNER JOIN products pr ON p.id = pr.point_id
INNER JOIN verifies v ON pr.id = v.product_id
GROUP BY p.name

Slow count query with large data in MySQL

2 Answers2