Quoted varchar() uses index, unquoted doesn't?

[anon_login_001]

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;

 

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';

 

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

[fenway]

I would run ANALYZE TABLE first to make sure everything's up to date... can we see the table structure?