Does a MySQL database use a composite index when one of the fields in the WHERE clause is null?

Question

Say I have a composite index consisting of the fields (commentId, journalEntryId, owning_comment_id ).

Now let's say that I have the following query:

UPDATE comments c 
    SET c.acceptedAsAnswer = isAnswer 
WHERE c.id = commentId
AND c.journal_entry_id = journalEntryId 
AND c.owning_comment_id IS NULL;

As you can see c.owning_comment_id should be NULL. Will the database still use the composite index in this case?

You should run `EXPLAIN` on this query to find out for yourself. Actually, I'm not sure that MySQL would even use this index at all, on the grounds that it doesn't appear to completely cover the `WHERE` clause. Do you have a typo in your update query? — Tim Biegeleisen, Sep 12 '20 at 13:10
@TimBiegeleisen thank you for the tip. The query does have a typo, = should be IS or else the EXPLAIN query will return 'impossible where'. The possible_keys column is listing my composite key, so i assume this key is being used. — Maurice, Sep 12 '20 at 13:30

score 0 · Accepted Answer · answered Sep 12 '20 at 18:25

This does not work as expected:

c.owning_comment_id = NULL

Instead, use

c.owning_comment_id IS NULL

Otherwise, the composite index should work. (And the order of columns in that index does not matter.)

The query you presented is incomplete -- what is cd? Please fix the query and qualify every column. There may be other issues.

Does a MySQL database use a composite index when one of the fields in the WHERE clause is null?

1 Answers1