CODEWITHSUNDEEP
mysqlsql
Hayi
Hayi

Reputation: 6296

Index on DATE(column)

if i use the following to compare a date with a datetime

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

can i still use a index on startTime ?

after i create a index : 

create index mi_date on calendar(startTime);

explain result :

+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | calendar     | index | NULL          | mi_date | 6       | NULL |   25 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+

and with query

SELECT * FROM `calendar` WHERE startTime like '2010-04-29 %'

explain cmd :

+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | calendar     | index | mi_date       | mi_date | 6       | NULL |   25 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+

the diference between the first explain is that column possible_keys is not null.

And there is also this query :

SELECT * FROM `calendar` WHERE startTime BETWEEN '2010-04-29 00:00:00' AND '2010-04-29 23:59:59'

explain :

+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | calendar     | range | mi_date       | mi_date | 6       | NULL |   16 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+

the column type is range and the rows scanned are less just 16 which is the result of my query.

p.s : i have 25 rows in my table.

Upvotes: 0

Views: 160

Answers (2)

javier_domenech
javier_domenech

Reputation: 6273

You can know it adding "Explain" before your query, this will report with keys are being used. Usually the use of functions disables indexed searchs, but you could perfectly use indexes modifying your query:

SELECT * FROM `calendar` WHERE startTime like '2010-04-29 %'

Well, as you can see in the results of the Explain, in this case (yours, using date(..) ) mysql is already using the index. Check the ref field:

ref – Shows the columns or constants that are compared to the index named in the key column. MySQL will either pick a constant value to be compared or a column itself based on the query execution plan. You can see this in the example given below.

Which is better? At this point they must be likely the same. You can try other queries and take a look at the rows field.

rows – lists the number of records that were examined to produce the output. This Is another important column worth focusing on optimizing queries, especially for queries that use JOIN and subqueries.

The on with less rows examined is the best.

Upvotes: 1

user4420255
user4420255

Reputation:

With EXPLAIN

mysql> EXPLAIN SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

you can check index using from mysql. But normaly MySQl dont' use a index using DATE().

Try

SELECT * FROM `calendar` WHERE startTime BETWEEN '2010-04-29 00:00:00' AND '2010-04-29 23:59:59'

Upvotes: 3

Related Questions