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