CODEWITHSUNDEEP
mysqlindexingquery-optimizationquery-performance
Aamir
Aamir

Reputation: 758

How to optimize mysql query using index?

I have following query which is taking 3 seconds to run.

SELECT c.kpi_name as kpi ,count(*) as cnt ,c.only_date as date 
FROM cellwise_performance c 
WHERE c.kpi_name ='DL Cell Throughput (Mbps)' 
      AND c.only_date BETWEEN '2014-06-10' AND '2014-11-05' AND c.kpi_value>=5   
GROUP BY c.kpi_name,month(c.only_date)

union all

SELECT c.kpi_name as kpi ,count(*) as cnt ,c.only_date as date 
FROM cellwise_performance c 
WHERE c.kpi_name ='DL Cell Throughput (Mbps)' 
      AND c.only_date BETWEEN '2014-06-10' AND '2014-11-05' AND c.kpi_value between 2 and 5 
GROUP BY c.kpi_name,month(c.only_date)

union all

SELECT c.kpi_name as kpi ,count(*) as cnt ,c.only_date as date 
FROM cellwise_performance c 
WHERE c.kpi_name ='DL Cell Throughput (Mbps)' 
      AND c.only_date BETWEEN '2014-06-10' AND '2014-11-05' AND c.kpi_value<2 
GROUP BY c.kpi_name,month(c.only_date);

explain plan for the query is :

---+---------+-------+-------+----------------------------------------------+
| id | select_type  | table        | type | possible_keys                | key      | key_len | ref   | rows  | Extra                                        |
+----+--------------+--------------+------+------------------------------+----------+---------+-------+-------+----------------------------------------------+
|  1 | PRIMARY      | c            | ref  | kpi_name,kpi_value,only_date | kpi_name | 203     | const | 72552 | Using where; Using temporary; Using filesort |
|  2 | UNION        | c            | ref  | kpi_name,kpi_value,only_date | kpi_name | 203     | const | 72552 | Using where; Using temporary; Using filesort |
|  3 | UNION        | c            | ref  | kpi_name,kpi_value,only_date | kpi_name | 203     | const | 72552 | Using where; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2,3> | ALL  | NULL                         | NULL     | NULL    | NULL  |  NULL |

Please help me out to optimize this query.

Upvotes: 0

Views: 38

Answers (2)

Aamir
Aamir

Reputation: 758

Ok I did something like that and query working like a charm -

select x.kpi,x.rating,date(x.date) as month,Count(*) `cnt` 
from ( select c.kpi_name as kpi ,
              case when c.kpi_value<2 then 0 
                   when c.kpi_value >= 5 then 2 
                   else 1 
              end `rating` ,
              c.only_date as date 
       from cellwise_performance c 
       where c.kpi_name ='DL Cell Throughput (Mbps)' 
         and c.only_date>='2014-06-10') as `x` 
 group by x.kpi,x.rating,month(x.date);

Upvotes: 1

Amadan
Amadan

Reputation: 198294

ALTER TABLE cellwise_performance ADD INDEX (kpi_name, kpi_value, only_date);

You need an index that catches all the columns you are searching by, or it can't optimise everything.

Upvotes: 1

Related Questions