Why sql "column like 'pattern%'" doesn't use the index?

Question

Tried 3 different ways.

1st

explain select * from test where create_time like '2019-10%';

id|select_type|table|partitions|type|possible_keys       |key|key_len|ref|rows |filtered|Extra      |
--|-----------|-----|----------|----|--------------------|---|-------|---|-----|--------|-----------|
 1|SIMPLE     |test |          |ALL |test_create_time_IDX|   |       |   |10376|   11.11|Using where|

2nd

explain select * from test where create_time between '2019-10-01 00:00:00' and '2019-10-31 23:59:59';

id|select_type|table|partitions|type |possible_keys       |key                 |key_len|ref|rows|filtered|Extra                |
--|-----------|-----|----------|-----|--------------------|--------------------|-------|---|----|--------|---------------------|
 1|SIMPLE     |test |          |range|test_create_time_IDX|test_create_time_IDX|6      |   |   5|     100|Using index condition|

3rd

explain select * from test where date_format(create_time,'%Y-%m') = '2019-10';

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra      |
--|-----------|-----|----------|----|-------------|---|-------|---|-----|--------|-----------|
 1|SIMPLE     |test |          |ALL |             |   |       |   |10376|     100|Using where|

I was told that "like 'pattern%'" will use the index."like '%pattern%'","like '%pattern'" won't.
But on the 1st condition it didn't work out as i thought.Is it because the column type of create_time is DATETIME?
And I can't find details about difference of all these "like" SQLs while interacting with indexes in official reference manual.
Can somenone pls share a official link with me?(All I got is hearsay)

Yes, it's because it's `DATETIME`. It's stored numerically, not as a string, so it first has to convert the date to a string before it can compare it to a `LIKE` pattern. — Barmar, Nov 15 '19 at 20:35
This is a duplicate but I'm all out of close votes. https://stackoverflow.com/questions/17101436/mysql-datetime-field-with-index-get-a-range-like-vs-between-and-performance — miken32, Nov 15 '19 at 20:39
You might like to know about this: [What makes a SQL statement sargable?](https://stackoverflow.com/q/799584/1115360) — Andrew Morton, Nov 15 '19 at 20:44

GMB · Accepted Answer · 2019-11-15T21:03:49.373

3

Consider this part of the documentation:

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

Side note: as documented here, most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees

In your examples:

create_time like '2019-10%'

You are comparing a date to a string. This requires MySQL to convert the data. As you can see in the output of the explain, an index can be used but it will be a full index scan. If create_time was a string, the index would be used with a range scan (which is more efficient).

create_time between '2019-10-01 00:00:00' and '2019-10-31 23:59:59'

between allows MySQL to optimize the use of index (range scan).

date_format(create_time,'%Y-%m') = '2019-10''

A function comes into play. The index will not be used.

edited Nov 15 '19 at 21:03

answered Nov 15 '19 at 20:43

GMB

216,147
25
84
135

*"You are comparing a date to a string. This requires MySQL to convert the data. Because, implicitly, a function comes into play, the index will not be used here. If create_time was a string, the index would be used."* Actually `create_time like '2019-10%'` can use a index but it will be a full index scan.. -> https://www.db-fiddle.com/f/rrTcypcsqrKLV7jBC7P7gQ/3 – Raymond Nijland Nov 15 '19 at 21:03
1

@RaymondNijland: yes you are correct (actually the OP posted the same explain as you did). I updated my answer, thanks you! – GMB Nov 15 '19 at 21:04
also the same goes for `date_format` -> https://www.db-fiddle.com/f/rrTcypcsqrKLV7jBC7P7gQ/5 which can also do a index scan.. – Raymond Nijland Nov 15 '19 at 21:05
1

But in general you want to indeed make the search condition *"sargable"* which `create_time between '2019-10-01 00:00:00' and '2019-10-31 23:59:59'` is that can use the index much better.. – Raymond Nijland Nov 15 '19 at 21:06

Why sql "column like 'pattern%'" doesn't use the index?

1 Answers1