mysql datetime field with index get a range 'like' vs. 'between and' performance

Question

I just find mysql can query datetime using like:

like '2013-06-12%'

I think it can not use the index. I Google it, but can not find such subject directly. So I have a test using a table with 3308614 records. The first SQL:

SELECT * FROM subscription t WHERE DATE(t.active_time) = '2013-06-30';

All we know this SQL can not use the index and it takes 4 seconds to get the result. The second SQL:

SELECT * FROM subscription t WHERE t.active_time LIKE '2013-06-30%';

I don't know if it can use the index, but it takes 4 seconds too. The third SQL:

SELECT * FROM subscription t WHERE t.active_time > '2007-11-30' AND t.active_time < '2007-12-01';

All we know the third SQL can use the index, and it takes 0.016 second.

So I think 'like' can not use the index when query the datetime field, because mysql should convert datetime field to string first and send the string to like command. Is this correct ?

Answer 1

Assuming that t.active_time's type is DATETIME,

The following query cannot use an index because of the function call. All active_time must be converted on-the-fly to a DATE value before comparison with '2013-06-30'. This string is converted to a DATE value in the very first place, and this happens only once at the very beginning of the query.

SELECT * FROM subscription t WHERE DATE(t.active_time) = '2013-06-30';

The second query cannot use an index either for similar reasons. You are actually making a string comparison (because of the LIKE operator). All active_time values are converted to a string on-the-fly.

SELECT * FROM subscription t WHERE t.active_time LIKE '2013-06-30%';

Only the last one can use an index. The strings '2007-11-30' and '2007-12-01' are cast to DATETIME in this case, because the < and > operators allow this.

SELECT * FROM subscription t WHERE t.active_time > '2007-11-30' AND t.active_time < '2007-12-01';

The latter also applies to the = and BETWEEN operators.

For information, all types can be compared to a string with the LIKE operator, suffering from the same problem as described above, because of the implicit conversion it requires.

t.active_time = '2013-06-30' does not work as expected because '2013-06-30' is cast to a DATETIME value, that is to say '2013-06-30 00:00:00'.

Answer 2

One alternative for such cases is to do something like this:

SELECT *
FROM subscription t
WHERE t.active_time BETWEEN '2013-06-30 00:00:00' AND '2013-06-30 23:59:59';

In the end you get all events from that day.