Why Index could refuse to work ?

Have you ever seen index which refused to be used even if there is every reason for it to work (from the glance view):

mysql> explain select * from article where article_id=10;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | article | ALL  | PRIMARY       | NULL | NULL    | NULL | 93490 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from article where article_id=10;

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

Why on the earth index would not be used you would think, even if MySQL is mentioning it in “possible keys” ? Should you try to force it ?

mysql> explain select * from article force index (PRIMARY) where article_id=10;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | article | ALL  | PRIMARY       | NULL | NULL    | NULL | 93490 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from article force index (PRIMARY) where article_id=10;

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

No Luck. Even Force Index can’t cure the problem. So what could it be ?

Lets take a look at article table:

CREATE TABLE `article` (
  `article_id` varchar(20) NOT NULL,
  `dummy` varchar(255) NOT NULL default 'dummy',
  PRIMARY KEY  (`article_id`)
)

CREATE TABLE `article` (

`article_id` varchar(20) NOT NULL,

`dummy` varchar(255) NOT NULL default 'dummy',

PRIMARY KEY (`article_id`)

)

As you can see article_id is VARCHAR and this is the problem. Comparing String to Number is not going to use the index. Lets check if your guess is right:

mysql> explain select * from article where article_id="10";
+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | PRIMARY       | PRIMARY | 62      | const |    1 | Using where |
+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from article where article_id="10";

+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+

+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+

+----+-------------+---------+------+---------------+---------+---------+-------+------+-------------+

1 row in set (0.00 sec)

Looks much better does not it ?

So why you would define something as VARCHAR and when to refer to it as an INTEGER ? I see no good reason but It is quite frequently seen in applications. Might be designers just think – lets put it VARCHAR so it will fit strings if we need it to, but later decide to stick to numbers.

If you’re storing INTEGER it is much better to define your columns as INT for many reasons, but if you decided to go with VARCHAR (ie you need leading zeroes to be preserved) you should refer to it as a sting in your application by using “”.

You may ask why MySQL can’t use index in this case, simply by converting number to the string and performing index lookup ?
This can’t be done as it would result in wrong result for some queries. The thing is there are multiple strings possible for single integer value – for example for there is number 5 and strings “5”, “05” “0005” “5.0” which all have same numeric value but different strings – this is why simple coversion to the string does not work.

Interesing enough it works other way around – you can refer to integer column as a string in most cases and MySQL will use the index, as for any string there is only one number which matches it. I guess this causes a lot of confusions – having seen it working in one direction people assume it also works in reverse one.

To add confusion MySQL mentions key as “possible keys” while really it has no way to use it for lookup (it can do index scan though). I guess “possible keys” are calculated before type matching is checked.

So be careful to use matching reference types in your applications 🙂

22 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Daniel Schneller

17 years ago

Yes, this is really a great one. I have seen this in an application that performed searches on tables with millions of records and even when specifiying the exact primary key value, it took tens of seconds, not to mention joins.
From my experience the “let’s take a varchar” approach is often taken by people who come from simpler systems, e. g. (in my example) Foxpro, where they stored nearly everything as character values.

Peter Zaitsev

Author

17 years ago

I did not mention it in the article but a lot of such problems also come from comparing incompatible columns, ie varchar to integer which may require closer look to figure out. I would really like MySQL EXPLAIN to issue warning in such case or something similar so it is easier to track it down.

Yes this mistake is usually done by people having limited experience, at least with MySQL.

Parvesh

16 years ago

Sorry for commenting on an old post. But recently, I saw another interesting thing. You are right about indexes not being used in this case, but it works the other way round. If the column is an integer and the query is something like

SELECT * FROM article WHERE article_id=”10″;

My first intuition was that this will also fail, but MySQL is smart enough 🙂

Peter Zaitsev

Author

16 years ago

No this one is actually fine, because there is only one number for given string “10” is 10 but it is not the case other way around 10 can correspond to “10” “010” “10.0” and a lot of other strings.

Jonathan

15 years ago

Thank you, this is a great post. It just caught me in a system!

Suman

15 years ago

What will happen if the table is:
CREATE TABLE article (
article_id int(5) NOT NULL,
dummy varchar(255) NOT NULL DEFAULT ‘dummy’,
PRIMARY KEY (article_id)
)
And query is:
SELECT * FROM article force INDEX (PRIMARY) WHERE article_id=’10’; (single quotes)
Will the index be used?

Suman

15 years ago

Sorry this has been answered above, I missed it.

Peter Mouland

14 years ago

Hi, any other reasons why FORCE INDEX is ignored? I have a table with 100 columns which about 25% are integers and most of those have indexes on them. A varchar column is being used as to left join to another table which also has an (btree) index on it, but for some reason it is not being used. EXPLAIN also doesn’t tell me about any possible keys. The table has about 100,000 rows, so not huge but be steadily growing. any ideas?? thanks,pete

Peter Zaitsev

Author

14 years ago

Pete,

This may happen if MySQL can’t use this index for the query.
FORCE INDEX only works if it is possible to use index but MySQL choses not to do that.

mylesmg

14 years ago

One reason not in this post, but in another post is that if you are joining tables, the join may not use an index if the tables are of different types (e.g. joining an InnoDB table to a Memory table). Simply changing the memory to innodb or vice versa will eliminate the issue.

Mark Cotner

12 years ago

This came up in a google search for a problem I was having where even if I forced I couldn’t get it to use an index. I quietly read the ENTIRE article, assuming this couldn’t possibly be it . . . surely we’re smarter than that.

Went back and checked anyway, sure enough . . . varchar for a universal_id field. I guess some of them must have characters in them(we don’t assign them). Added quotes to the where clause and sped it up 300x.

Thanks for posting this. It may seem obvious, but I surely didn’t catch it.

Pascal

11 years ago

The same problem i have, at the only difference, that my column is Already an INT….
What should i do?

Thanks!
Pascal

Yakovenko Stepan

11 years ago

Would you be so kind to take a look here: http://stackoverflow.com/questions/13691422/mysql-with-huge-tables-2-queries-faster-then-1-index-not-used ? Can this happen to bigint field?

sivamurugan

10 years ago

Thanks a lot Peter Zaitsev.. It saves me in getting late of 6 million records…

grandman

8 years ago

Thanks Peter.

Example column field is type INT, name TestID, (it’s also an index). Is it then better to search as:
WHERE table_name.TestID = ‘5’
or
WHERE table_name.TestID = 5
I know that you wrote:

“Interesing enough it works other way around – you can refer to integer column as a string in most cases and MySQL will use the index”

What’s best solution for this (what is working faster)? I have possibility to skip quotes. Should I?

Pepijn

6 years ago

We are actually experiencing this with a Magento 1 shop right now.
Magento stores the increment_id as string and with over 2 million orders in the sales_flat_order table queries were starting to take exceptionally long.

time mysql -e “SELECT * FROM sales_flat_order WHERE increment_id=202405845”
real 0m30.920s

time mysql -e “SELECT * FROM sales_flat_order WHERE increment_id=’202405845′”
real 0m0.006s

Yes, that’s almost 31 SECONDS DIFFERENCE! There must be a bug somewhere in Magento/Zend Framework 1 which determines if the value needs quotes and decides against it, since it’s a number anyway, or we have some wonky third-party module doing this.

Maybe a lesson learned: don’t use only integers in Magento order numbers! 🙂

Tymoteusz Motylewski

6 years ago

Is it possible to make MySQL log queries passing wrong datatypes? Or maybe there is other way to find issues like that?

5 years ago

it’s an old post but let me get it right.
MySQL does not use the index because of the unknown results: “for example for there is number 5 and strings “5”, “05” “0005” “5.0” which all have same numeric value but different strings”…
but what is really happened is that the query run, scan the entire table (full table scan) and give me the “wrong” results anyway !
so for my opinion, data conversion should not impact index comparison because it impact performance.