CODEWITHSUNDEEP
mysql
noufalcep
noufalcep

Reputation: 3534

How can i optimize the following mysql query?

I want to get the sum of amount of each brand

How can i optimize the query?

SELECT a1,a2,a3,a4,a5 
    FROM (
      (SELECT SUM(amount) as a1  FROM `category_data` WHERE brand ='brand1' ) AS t1,
      (SELECT SUM(amount) as a2  FROM `category_data` WHERE brand ='brand2' ) AS t2,
      (SELECT SUM(amount) as a3  FROM `category_data` WHERE brand ='brand3' ) AS t3,
      (SELECT SUM(amount) as a4  FROM `category_data` WHERE brand ='brand4' ) AS t4,
      (SELECT SUM(amount) as a5  FROM `category_data` WHERE brand ='brand5' ) AS t5
    )

table

name    brand     amount
--------------------------
name1   brand1    1000
name2   brand2    2000
name3   brand1    1200 ...........

Upvotes: 0

Views: 46

Answers (2)

spencer7593
spencer7593

Reputation: 108500

The knee jerk answer is to ensure you have a covering index, with a leading column of brand, and including the amount column, for example:

... ON category_data (brand, amount)

The query could also be re-written

SELECT SUM(IF(t.brand='brand1',t.amount,NULL)) AS a1
     , SUM(IF(t.brand='brand2',t.amount,NULL)) AS a2
     , SUM(IF(t.brand='brand3',t.amount,NULL)) AS a3
     , SUM(IF(t.brand='brand4',t.amount,NULL)) AS a4
     , SUM(IF(t.brand='brand5',t.amount,NULL)) AS a5
  FROM `category_data` t
 WHERE t.brand IN ('brand1','brand2','brand3','brand4','brand5')

This might perform better, especially if MySQL isn't making use of an index with the original query, and is performing a full scan of the category_data table five times.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You can use a CASE statement in a single query instead of running 5 queries 

SELECT 
SUM(CASE WHEN brand ='brand1'  THEN amount ELSE 0 END ) a1,
SUM(CASE WHEN brand ='brand2'  THEN amount ELSE 0 END ) a2,
SUM(CASE WHEN brand ='brand3'  THEN amount ELSE 0 END ) a3,
SUM(CASE WHEN brand ='brand4'  THEN amount ELSE 0 END ) a4,
SUM(CASE WHEN brand ='brand5'  THEN amount ELSE 0 END ) a5
FROM `category_data`

Also using aggregate function without grouping them will consider whole table data a one group so you should look at GROUP BY (Aggregate) Functions

Upvotes: 2

Related Questions