# Indexing oddity

**URL:** <https://forums.percona.com/t/indexing-oddity/1534>\
**Category:** Other MySQL® Questions\
**Created:** [October 18, 2010, 12:58pm UTC](https://forums.percona.com/t/indexing-oddity/1534 "2010-10-18T12:58:22Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![crdiaz324](https://avatars.discourse-cdn.com/v4/letter/c/f05b48/32.png) [@crdiaz324](https://forums.percona.com/u/crdiaz324)\
**Post date:** [October 18, 2010, 12:58pm UTC](https://forums.percona.com/t/indexing-oddity/1534/1 "2010-10-18T12:58:22Z")

</div>

Hi all,

I am running into some weird behavior with the mysql optimizer choosing to do a full table scan instead of choosing the primary key index for this query:

mysql\> explain SELECT \* FROM route WHERE route\_id = 30809866;  
±—±------------±------±-----±--------------±-----±- -------±-----±-------±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±-----±--------------±-----±- -------±-----±-------±------------+  
| 1 | SIMPLE | route | ALL | PRIMARY | NULL | NULL | NULL | 383400 | Using where |  
±—±------------±------±-----±--------------±-----±- -------±-----±-------±------------+

The table looks like this:

CREATE TABLE `route` (  
`route_id` char(10) CHARACTER SET utf8 COLLATE utf8\_bin NOT NULL,  
`type` tinyint(4) NOT NULL,  
`event_type` tinyint(4) NOT NULL,  
`title` varchar(128) COLLATE utf8\_unicode\_ci NOT NULL DEFAULT ‘’,  
`description` text COLLATE utf8\_unicode\_ci,  
`start_instructions` text COLLATE utf8\_unicode\_ci,  
`user_id` varchar(64) COLLATE utf8\_unicode\_ci NOT NULL,  
`site_id` varchar(64) COLLATE utf8\_unicode\_ci NOT NULL,  
\<snip – more columns where\>  
PRIMARY KEY (`route_id`),  
KEY `site_id` (`site_id`,`user_id`),  
KEY `type` (`type`),  
KEY `event_type` (`event_type`),  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8\_unicode\_ci

As you can see, the primary key has an index on route\_id, however mysql seems to skip it and instead do a full table scan. Any ideas why this could be?

---

<div class="post-metadata">

**Author:** ![justin.swanhart](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/justin.swanhart/32/16904_2.png) [@justin.swanhart](https://forums.percona.com/u/justin.swanhart)\
**Post date:** [October 18, 2010, 1:13pm UTC](https://forums.percona.com/t/indexing-oddity/1534/2 "2010-10-18T13:13:40Z")

</div>

Hi,

Try enclosing the value in quotes. The primary key is CHAR() column, not an integer column.

---

<div class="post-metadata">

**Author:** ![crdiaz324](https://avatars.discourse-cdn.com/v4/letter/c/f05b48/32.png) [@crdiaz324](https://forums.percona.com/u/crdiaz324)\
**Post date:** [October 18, 2010, 1:17pm UTC](https://forums.percona.com/t/indexing-oddity/1534/3 "2010-10-18T13:17:01Z")

</div>

When I use quotes, it comes up with a different issue:

mysql\> explain SELECT \* FROM route WHERE route\_id = ‘30809866’;  
±—±------------±------±-----±--------------±-----±- -------±-----±-----±------------------------------------- ---------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±-----±--------------±-----±- -------±-----±-----±------------------------------------- ---------------+  
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |  
±—±------------±------±-----±--------------±-----±- -------±-----±-----±------------------------------------- ---------------+  
1 row in set (0.00 sec)

One thing I noticed is that if I don’t quote the route\_id value and use an order by clause it seems to use the index correctly:

mysql\> explain SELECT \* FROM route WHERE route\_id = 30809866 order by route\_id;  
±—±------------±------±------±--------------±------- -±--------±-----±-------±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±------±------±--------------±------- -±--------±-----±-------±------------+  
| 1 | SIMPLE | route | index | PRIMARY | PRIMARY | 30 | NULL | 356424 | Using where |  
±—±------------±------±------±--------------±------- -±--------±-----±-------±------------+  
1 row in set (0.00 sec)

---

<div class="post-metadata">

**Author:** ![crdiaz324](https://avatars.discourse-cdn.com/v4/letter/c/f05b48/32.png) [@crdiaz324](https://forums.percona.com/u/crdiaz324)\
**Post date:** [October 18, 2010, 1:31pm UTC](https://forums.percona.com/t/indexing-oddity/1534/4 "2010-10-18T13:31:03Z")

</div>

That makes sense. Thanks Justin.

-Carlos

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [October 20, 2010, 6:39am UTC](https://forums.percona.com/t/indexing-oddity/1534/5 "2010-10-20T06:39:08Z")

</div>

| [B]Quote:[/B] |
| Your second query will range scan the primary key (due to the ORDER BY) and only return the rows that match (which will be no rows). |

The primary key is the table itself. Both with and without ORDER BY, the entire table will be read. The query with WHERE route\_id = 30809866 can return many rows.

The problem is that you are comparing a string with an integer. Evaluation of the clause WHERE route\_id = 30809866 will convert route\_id to an integer before performing the comparison. Many different strings will equal 30809866 after conversion. Moreover, you will run into problems because the numbers you use can be larger than the maximum size of an integer, resulting in the use of (inaccurate) floats. Either use integers and use an integer field, or switch to char(10) and use strings in your query. E.g. if you know that route\_id’s are padded with zeros, use WHERE route\_id = ‘0030809866’