•  

Announcement

Announcement Module
Collapse
No announcement yet.

IS NOT NULL condition - MySQL doesn't use index

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • IS NOT NULL condition - MySQL doesn't use index

    #1
    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?
    Tags: None

  • #2
    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.

    Comment

    Working...
    X