What is type ref_or_null in MySQL EXPLAIN?

Performance Classification: good

MySQL uses the ref_or_null strategy always when an index is used with an additional NULL condition: First, the index is searched for the requested value and then again with another step to find all values matching NULL.

Examples

✅ When it is used

CREATE TABLE products (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    discount tinyint UNSIGNED,
    INDEX(discount)
);

SELECT * FROM products WHERE discount = 0 OR discount IS NULL;

The ref_or_null strategy is used because the index on discount has been used to search for values with a specific or NULL.

❌ When it is not used

CREATE TABLE loans (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    issuer varchar(100),
    INDEX(issuer)
);

SELECT * FROM loans WHERE issuer = 'Sakila';

For this example, the ref instead of ref_or_null strategy will be used because no NULL check is part of the conditions. Furthermore, the ref strategy will be faster because MySQL doesn't have to do another index check for the NULL value.

How to optimize a query using `ref_or_null`?

A query using ref_or_null already uses an index so there is no immediate call for action. Nevertheless, you can optimize this query with some simple steps - if possible:

Check the filtered column for a high percentage value to ensure that most rows loaded with the index are also used and not thrown away after checking all conditions
Drop the NULL condition if it is not needed for your query

Conclusion

Your query uses the ref_or_null strategy for a good reason: You're checking explicitly for NULL values in a column. A better-fitting index may reduce the number of rows loaded, but you will always keep the ref_or_null strategy.