No index used warning in WHERE column IS NULL

Question

I'm getting this warning:

No index used in query/prepared statement SELECT * FROM mTable WHERE my_column IS NULL

But the my_column is already indexed.

MariaDB [mytest]> SHOW INDEX FROM mTable;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mTable |          0 | PRIMARY   |            1 | id          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| mTable |          1 | my_column |            1 | my_column   | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Reason maybe is my_column actually contains NULL values so there's literally no index that's why MySQL complains? But then I have to query columns that are NULL.

Any ideas for a better approach on this?

Answer 1

And that is a reason why you should take the "no index used" warning with a grain of salt.

When you say one of these with INDEX(col):

WHERE col IS NULL
WHERE col = 123

and the test is true more than about 20% of the rows in the table, the Optimizer will eschew the index and simply scan the table.

Why? When using an index, there is a back-and-forth action between scanning the index and looking up the row in the data. Those are two separate BTrees. The Optimizer makes a guess as to whether it would be faster to do the back-and-forth versus simply scanning all the rows, tossing those that fail the test.

The cutoff is often around 20%, but I have see anywhere from 10% to 30%.

Answer 2

You should create and index anyway.

Run the following query:

SELECT COUNT(1) rowcount,my_column FROM mTable GROUP BY my_column
UNION SELECT COUNT(1) rowcount,'Whole Table' FROM mTable;
SELECT DISTINCT my_column 'Cardinality' from mTable;

If the number of rowcount for NULL is more than 5% of Whole Table, an index might not be used be the Query Optimizer if your WHERE clause is AND my_column IS NULL or AND my_column IS NOT NULL (especially if the number of rows with NULL is about the same as the number of rows with non-NULL). If the cardinality of my_column is very low (indicating a small number of unique values) an index on my_column may never be be used at all.

That warning you mentioned may go away if you create the index on my_column, but the index may never be used if the circumstances I mentioned apply.

The index may be of value if NULL my_column is like 90% of the table.

My recommendation is not to make one at this time.