Indexes are not getting used in MySQL query

Question

I have written the query below and it's taking nearly 5 minutes to run. I have 6 million rows of data in table and found from the execution plan that some how my query does not use indexes even though all fields of the table have indexes.

Query

SELECT 
  event_date as date,   
  (CAST('2014-05-31' AS DATE)- INTERVAL 5 MONTH + INTERVAL 1 DAY) AS FROM_DATE,
  COUNT(DISTINCT(IF( Column1 !=0 OR Column2!=0 OR Column3 !=0, account, NULL))) AS total_account1,
  COUNT(DISTINCT(IF( Column4 !=0 OR Column5 !=0 OR Column6!=0, account, NULL))) AS total_account2,
  COUNT(DISTINCT(IF( Column7 !=0 OR Column8 !=0 OR Column9!=0, account, NULL))) AS total_account3
FROM Table_name
WHERE cast(event_date as DATE) BETWEEN CAST('2014-05-31' AS DATE)- INTERVAL 5 MONTH and CAST('2014-05-31' AS DATE)
  AND cast(event_date as DATE) < NOW() - INTERVAL 2 DAY
GROUP BY MONTH(event_date)

"Explain" above query output is -

+----+-------------+---------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | table_name | ALL  | NULL          | NULL | NULL    | NULL | 5764552 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+---------+-----------------------------+

Why is my query not using the indexes available to it?

Can you confirm you are using same order in where clause as of index column order? If not please change as per index column order in where clause, then it should improve. — Sushant Tambare, Oct 07 '14 at 11:04
In where clause I am using event_Date which is primary key in table. — Hrushi, Oct 07 '14 at 12:09
http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable I believe it is because the way you have written you where clause is making this query non sargable. — John Powell, Oct 28 '14 at 09:46

score 0 · Answer 1 · answered Oct 07 '14 at 11:12

0

You can explicitly force engine to use index.

check it http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

answered Oct 07 '14 at 11:12

Ronak Shah

1,539
2
13
20

This doesn't answer the question, though it is true. – John Powell Oct 28 '14 at 09:44

Indexes are not getting used in MySQL query

1 Answers1