MySQL negeert index bij onjuiste type in where clause

Het heeft even geduurd sinds het laatste blog maar ik liep al wel een tijdje met een onderwerp om over te bloggen. Het was me namelijk een keer opgevallen dat MySQL een index niet gebruikt als het type van de kolom niet overeenkomt met de waarde waar je op zoekt.

Neem bijvoorbeeld een tabel ‘book’ met een kolom ‘isbn’ en ‘title’:

CREATE TABLE IF NOT EXISTS `book` (
 `isbn` char(13) NOT NULL,
 `title` varchar(255) NOT NULL,
 PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `book` (`isbn`, `title`) VALUES
('9789021400730', 'De Amerikaanse prinses'),
('9789045028163', 'Dit kan niet waar zijn');

Als je in deze tabel een record opzoekt aan de hand van het ISBN doe je dat normaal gesproken met de waarde tussen aanhalingstekens, want je zoekt immers op een string. In dat geval gebruikt MySQL ook netjes de gedefineerde index (de primary key in dit geval):

EXPLAIN SELECT * FROM `book` WHERE isbn = '9789045028163'
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE book const PRIMARY PRIMARY 13 const 1

Maar als je per ongeluk (of omdat je ORM-tool niet goed werkt) zoekt op een integer, wordt de index niet gebruikt:

EXPLAIN SELECT * FROM `book` WHERE isbn = 9789045028163
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE book ALL PRIMARY NULL NULL NULL 2 Using where

Het resultaat van beide queries is hetzelfde; er zal in beide gevallen 1 record worden gevonden (Dit kan niet waar zijn) maar als er gebruik wordt gemaakt van de index (primary key) is dat natuurlijk veel sneller. Zeker als er duizenden records in je tabel zitten.