Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Index not used when varchar() not quoted?

anon_login_001anon_login_001 EntrantCurrent User Role Beginner
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!</pre>

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 </pre>

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


  • teajay2teajay2 Entrant Current User Role Beginner
    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.
  • anon_login_001anon_login_001 Entrant Current User Role Beginner
    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. = )
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.