# why coerce the column instead of a constant?

**URL:** <https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341>\
**Category:** Other MySQL® Questions\
**Created:** [February 4, 2010, 1:09pm UTC](https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341 "2010-02-04T13:09:41Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [February 4, 2010, 1:09pm UTC](https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341/1 "2010-02-04T13:09:41Z")

</div>

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.

---

<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:** [February 4, 2010, 2:58pm UTC](https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341/2 "2010-02-04T14:58:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [February 4, 2010, 4:16pm UTC](https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341/3 "2010-02-04T16:16:53Z")

</div>

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

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

---

<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:** [February 4, 2010, 6:04pm UTC](https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341/4 "2010-02-04T18:04:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [February 4, 2010, 8:41pm UTC](https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341/5 "2010-02-04T20:41:34Z")

</div>

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

---

<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:** [February 5, 2010, 5:02pm UTC](https://forums.percona.com/t/why-coerce-the-column-instead-of-a-constant/1341/6 "2010-02-05T17:02:36Z")

</div>

the page is [URL][http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html[/URL]](http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html%5B/URL%5D)