 Index not used when varchar() not quoted? [message #3348] |
Mon, 28 July 2008 13:53  |
 anon_login_001
Messages: 4
Registered: July 2008
Location: Memphis |
Junior Member |
|
|
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. )
|
|
|
|
| Re: Index not used when varchar() not quoted? [message #3350 is a reply to message #3349 ] |
Tue, 29 July 2008 09:16  |
anon_login_001
Messages: 4
Registered: July 2008
Location: Memphis |
Junior Member |
|
|
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. = )
|
|
|
Members
Search
Help
Register
Login
Home
