Sql query indexing is not working properly

Question

I am facing a problem with indexing. I am not getting why the indexing is not working for the same query some times.

In below case indexing is working

EXPLAIN SELECT `table_name`.* FROM `table_name`
WHERE (created_date between '2018-08-11' and '2018-08-11');

| id | select_type | table | partitions | type |

possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | table_name | NULL | ref | index_table_name_created_date | table_name_created_date | 4 | const | 11 | 1.00 | Using index condition; Using where | +----+-------------+-------------------+------------+------+--------------------------------------+--------------------------------------+---------+-------+------+----------+------------------------------------+ 1 row in set, 1 warning (0.01 sec)

Same thing if i increase the date range indexing is not working like below one

EXPLAIN SELECT `table_name`.* FROM `table_name`
WHERE (created_date between '2018-08-11' and '2018-09-11');
+----+-------------+-------------------+------------+------+--------------------------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------+------------+------+------------------ | 1 | SIMPLE | table_name | NULL | ALL | index_table_name_created_date | NULL | NULL | NULL | 1233 | 0.30 | Using where | +----+-------------+-------------------+------------+------+--------------------------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

Can anyone help me how to handle this case? Because of this to my query is taking longer time to execute.

Answer 1

The major concept here is that you are doing SELECT * on your table, which tells MySQL that you want to return all columns from the table, for each record returned. While you may have an index on the created_date column, MySQL may choose not use it with SELECT *. The reason for this is that once MySQL traverses down the B-tree to reach each leaf node, only data for the created_date is present there. For the other columns, MySQL would have to do an additional seek to the clustered index to get the data for the other columns. As a result, using the index might not help the query.

As to why the index was used in the first case, the optimizer may have recognized that there was only a single matching record, and then decided to use the index anyway. Really, your example would be better if many records were being returned from the query.

Here is an example of a query which could use the index:

SELECT created_date
FROM yourTable
WHERE created_date BETWEEN '2018-08-11' AND '2018-08-11';

In this case, the index contains all the columns we are trying to select. If you wanted to select another column, say col1, then you could add col1 to the index (such that the index would now be on (created_date, col1)), and then the following query could also use the index:

SELECT created_date, col1
FROM yourTable
WHERE created_date BETWEEN '2018-08-11' AND '2018-08-11';