Mysql – No index used warning in WHERE column IS NULL

indexindex-tuningmariadbMySQL

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?

Best Answer

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.

Related Question