Mysql - "Select like" not using index

Question

I have been playing around with indexes on MySQL (5.5.24, WinXP), but I can't find the reason of why the server is not using one index when a LIKE is used.

The example is this:

I have created a test table:

create table testTable (
  id varchar(50) primary key,
  text1 varchar(50) not null,
  startDate varchar(50) not null
) ENGINE = innodb;

Then, I added an index to startDate. (Please, do not ask why the column is a text and not a date time.. this is just a simple test):

create index jeje on testTable(startdate);
analyze table testTable;

After that, I added almost 200,000 rows of that where startDate had 3 possible values. (One third of appearences for each one..near 70,000 times)

So, if I run an EXPLAIN command like this:

explain select * from testTable use index (jeje) where startDate = 'aaaaaaaaa';

The answer is the following:

id = 1
select_type = SIMPLE
type = ref
possible_keys = jeje
key = jeje
rows = 88412
extra = Using where

So, the key is used, and the rows amount is near to 200,000/3 so all is ok.

The poblem is that if I change the query to: (just chaning '=' to 'LIKE'):

explain select * from testTable use index(jeje) where startDate LIKE 'aaaaaaaaa';

In this case, the answer is:

id = 1
select_type = SIMPLE
type = ALL
possible_keys = jeje
key = null
rows = 176824
extra = Using where

So, the index is not being used now(key is null, and rows near to the full table..as the type=all suggests).

MySQL documentation says that LIKE DOES make use of indexes.

So, what am i not seeing here? Where is the problem?

Thanks for your help.

Rally

Answer 1

Based on Ubik comment, and data changes, I found that: The Index IS used in these cases:

- explain select * from testTable force index jeje where startDate like 'aaaaaaadsfadsfadsfasafsafsasfsadsfa%';
- explain select * from testTable force index jeje where startDate like 'aaaaaaadsfadsfadsfasafsafsasfsadsfa%';
- explain select * from testTable force index jeje where startDate like 'aaa';

But the index is NOT being used when I use this query:

- explain select * from testTable force index jeje where startDate like 'aaaaaaaaa';

Based on the fact that in startDate column all the values have the same length (9 characters), when I use a query using a LIKE command and a 9 characters constant, PERHAPS MySQL prefer to not use the reason because of some performance algorithm, and goes to the table.

My concern was to see if I was making some kind of mistake on my original tests, but now I think that the index and tests are correct, and that MySQL in some cases decides to not use the index... and I will relay on this.

For me, this is a closed task. If somebody want to add something to the thread, you are welcome.

Rally

Answer 2

MySql can ignore index if it index incurs access to more than 30% of table rows. You could try FORCE INDEX [index_name], it will use index in any case.

The value of sysvar_max_seeks_for_key also affects whether the index is used or not:

• http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_seeks_for_key

  Try changing this value to a smaller number.

Search for similar requests on SO.

Guillot

Thanks Ubik for your answer. Based on this, I changed the table data to near to 300k rows, with 5 possible values on startDate column (20% each). – Rally

Your answer didn't help resolve the problem, but it did help me to think in different approachs. So far, I don't have a "solution", but one idea of what can be the root of this. See my own answer. Thank for your help!. – Rally