# Index not used when varchar() not quoted?

**URL:** <https://forums.percona.com/t/index-not-used-when-varchar-not-quoted/856>\
**Category:** Other MySQL® Questions\
**Created:** [July 28, 2008, 12:23pm UTC](https://forums.percona.com/t/index-not-used-when-varchar-not-quoted/856 "2008-07-28T12:23:31Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![anon\_login\_001](https://avatars.discourse-cdn.com/v4/letter/a/ac91a4/32.png) [@anon\_login\_001](https://forums.percona.com/u/anon_login_001)\
**Post date:** [July 28, 2008, 12:23pm UTC](https://forums.percona.com/t/index-not-used-when-varchar-not-quoted/856/1 "2008-07-28T12:23:31Z")

</div>

I have an index on this table that contains the ‘deleted’ tinyint() column first, followed by the ‘parent\_id’ varchar(36) column.

When I run this query:

SELECT \* FROM registration\_task WHERE name like ‘%address%’ and deleted=0 and status!=‘Completed’ and status!=‘Not Applicable’ and parent\_id=120142; – THIS LINE!

an EXPLAIN tells me that it uses the correct index, with a key\_len of ‘1’, and that it searches 157,000-some-odd records. Obviously, using only the ‘deleted’ column of the index.

When I add quotes in the query around the parent\_id, as such:

SELECT \* FROM registration\_task WHERE name like ‘%address%’ and deleted=0 and status!=‘Completed’ and status!=‘Not Applicable’ and parent\_id=‘120142’; – THIS LINE

an EXPLAIN shows that the key\_len used was 39 (deleted and the parent\_id) and it only intends to search ‘1’ row.

Can someone please enlighten me about this behavior? I haven’t really come accross it before, and am just looking for an explanation of why an Index wouldn’t get utilized just because of an unquoted parameter.

(though, I know that the param should have been quoted in the first place… I stumbled upon this in the slow query log, and was experimenting for optimization. )

---

<div class="post-metadata">

**Author:** ![teajay2](https://avatars.discourse-cdn.com/v4/letter/t/3e96dc/32.png) [@teajay2](https://forums.percona.com/u/teajay2)\
**Post date:** [July 28, 2008, 9:26pm UTC](https://forums.percona.com/t/index-not-used-when-varchar-not-quoted/856/2 "2008-07-28T21:26:11Z")

</div>

My guess is that because there is an implicit conversion of int to varchar taking place, it is that conversion that is preventing the index from being utilized as effectively.

why is your parent\_id a varchar field if it seems to contain id numbers? i believe making this a numeric field would make this overall more efficient.

---

<div class="post-metadata">

**Author:** ![anon\_login\_001](https://avatars.discourse-cdn.com/v4/letter/a/ac91a4/32.png) [@anon\_login\_001](https://forums.percona.com/u/anon_login_001)\
**Post date:** [July 29, 2008, 7:46am UTC](https://forums.percona.com/t/index-not-used-when-varchar-not-quoted/856/3 "2008-07-29T07:46:14Z")

</div>

I guessed that the conversion might be causing the issue, but I would think the conversion would take place before it tried to compare to what’s in the index.

As far as why it’s a varchar()… I’m dealing with a system that dynamically generates a lot of the structure, and normally parent\_id’s are the long unique-string type IDs (the name for that escapes me). In this case, there was existing data that got imported due to some heavy ‘customization’, therefore these particular IDs are not like the others.

Unfortunately, that problem is beyond my current scope to fix. Working within heavy constraints. = )