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" — Mike
– Mike, Commented Aug 16, 2014 at 5:35

Mike · Accepted Answer · 2014-08-16 05:41:18Z

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, 2014 at 5:41

Mike

3,8513 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chan Over a year ago

If verifies table have more condition like type = 2, how to do it?

Mike Over a year ago

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.

Edper · Accepted Answer · 2014-08-16 05:47:38Z

0

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

answered Aug 16, 2014 at 5:47

Edper

9,3621 gold badge32 silver badges49 bronze badges

Collectives™ on Stack Overflow

Slow count query with large data in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related