Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – indexing a column with mostly null values

I have a very big table with a column that has mostly null values.
I query this table a lot with x_column is not null condition.

I wonder how I should index it and what is the best way to improve performance here because I read in several places that is not null does not even use an index. So what can I do to use an index here?

See this or this for example, but there are more. I do not want to add another field (computed field) to solve it. Is there another, maybe newer way?

(I’m using innoDB)

Tags:

2

Answers


  1. 0

    I read in several places that is not null does not even use an index.

    What you read is not accurate.

    https://dev.mysql.com/doc/refman/8.4/en/is-null-optimization.html says:

    MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value.

    Likewise, IS NOT NULL is optimized like col_name <> constant_value. I.e. it’s a range access.

    Demo:

    I tested MySQL 8.4.2, but this should apply to MySQL 8.0 and 5.x as well.

    mysql> create table mytable (id serial primary key, v varchar(10), x int, key(v));

mysql> explain select * from mytable where v is not nullG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: range
possible_keys: v
          key: v
      key_len: 43
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

    Maybe the references you read that said is not null doesn’t use an index are based on some version of MySQL from 20 years ago, but all currently supported versions do use an index in this case.

    Login or Signup to reply.
  2. 0

    x_column is not null is similar to testing x_column is > 123 — it is a "range" test. Then the "range test" would probably decide that the index is not worth using. That is, the index might be used if very few of the rows have a non-null value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search