0

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?

  • "there's literally no index" disagrees with the SHOW you provide. – Rick James Jun 23 at 13:45
  • @RickJames What I mean is there's no actual index internally yet since most records are null, but then I don't know what's actually going on inside. – IMB Jun 23 at 15:03
  • 1
    For an INDEX, NULL is a value. There as many 'rows' in an index as there are rows in the data, regardless of whether some (or all) of the values are NULL. – Rick James Jun 23 at 15:18
0

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.

0

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%.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.