CODEWITHSUNDEEP
mysql
Chan
Chan

Reputation: 1959

Slow count query with large data in MySQL

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?

Upvotes: 0

Views: 96

Answers (2)

Edper
Edper

Reputation: 9322

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

Upvotes: 0

Mike
Mike

Reputation: 3811

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`

Upvotes: 1

Related Questions