why coerce the column instead of a constant?

[vgatto]

So if I have a table

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)?

Just curious.

[gmouse]

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’.

[xaprb]

gmouse is right. Strings are not compared the way you might think.

'1 ’ == ‘1’ == ‘1e0’ == ‘1.0000’ == ‘1 day I wrote a forum post’

[gmouse]

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.

[xaprb]

If that’s what you believe, then keep on believing )

[gmouse]

the page is http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html