Not the answer you need?
Register and ask your own question!

IS NOT NULL condition - MySQL doesn't use index

heyperfheyperf EntrantInactive User Role Beginner
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?


  • sterinsterin Mentor Inactive User Role Contributor
    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:

    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.
  • adolfainsley8adolfainsley8 Entrant Current User Role Beginner
    And then split it further and re-perform that operation until it finds the record that you are after???

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.