Index not used when varchar() not quoted?

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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