| IS NOT NULL condition - MySQL doesn't use index [message #2084] |
Wed, 17 October 2007 15:11  |
heyperf
Messages: 1
Registered: October 2007 |
Junior Member |
|
|
Why does MySQL ignore my index01 (key_part1, key_part2, key_part3) for the following query?
select * from tab where key_part1 is not null and key_part2 is null and key_part3 is null;
All fields are of type "datetime default NULL".
"explain" of this query returns "key: NULL". Why?
If I add "force index index01", explain returns "key: index01" but still shows "rows: 91261", which almost is the cardinality of the table.
If I change the first condition to "key_part1 is null", the index is used, explain returns "key: index01"
What can I do?
[Updated on: Wed, 17 October 2007 16:26]
|
|
|
| Re: IS NOT NULL condition - MySQL doesn't use index [message #2090 is a reply to message #2084 ] |
Wed, 17 October 2007 19:30  |
 sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
All conditions that negate something are basically impossible for the database to optimize.
Examples are your IS NOT NULL or col1 != 'something' etc.
The reason is that you are telling the database to find _all_ records where you don't have a match.
And the only way the DBMS can do this is by going through all records and throw away the once that doesn't match.
But if you are issuing a query where you say that you want all records matching a certain expression then the DBMS can use an index because it can jump to the middle of the index and immediately tell (since the records are sorted) if what you are searching for is before or after the current record.
And then split it further and re-perform that operation until it finds the record that you are after.
Some suggestion:
Don't use NULL values unless you need to.
Since NULL values are a second dimension they are much harder to work with.
Rewrite your query to search _for_ a certain value or values and not the _absence_ of a value.
For example:
Data:
0
0
0
0
0
1
2
3
4
5
If I want to return all records except the records that are 0 I can write that condition as:
... WHERE data > 0;
Instead of:
... WHERE data != 0;
And in the first expression I'm saying what I want and in the second I'm negating what I don't want.
|
|
|
Members
Search
Help
Register
Login
Home
