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?