CREATE TABLE examples ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, external_id CHAR(12) NOT NULL, data INT(11), INDEX (external_id)) Engine=InnoDB;
and I query it like so:
SELECT * FROM examples WHERE external_id = 1
I can’t use the index, because MySQL thinks it must coerce the external_id column to an INT in order to evaluate the where clause. Is there any good reason why it just doesn’t coerce the constant into a CHAR(12)?
It cannot simply use the index, because external_id = ‘asdf1’ would also match your where clause. Either convert external_id to an integer field, or use external_id = ‘1’.
Actually my comparison was wrong because it only looks at prefixes. And I believe MySQL could use an index, it should just make sure that the first character is a 1, and the second character is non-numeric.