The NOT NULL range scan optimization enables the optimizer to construct range scans from NOT NULL conditions that it was able to infer from the WHERE clause.
The optimization appeared in . It is not enabled by default; one needs to set an optimizer_switch flag to enable it.
A basic (but slightly artificial) example:
The WHERE condition in this form cannot be used for range scans. However, one can infer that it will reject rows that NULL for weight. That is, infer an additional condition that
and pass it to the range optimizer. The range optimizer can, in turn, evaluate whether it makes sense to construct range access from the condition:
Here's another example that's more complex but is based on a real-world query. Consider a join query
Here, the optimizer can infer the condition "return_id IS NOT NULL". If most of the orders are not returned (and so have NULL for return_id), one can use range access to scan only those orders that had a return.
The optimization is not enabled by default. One can enable it like so
TODO.
- JIRA bug report which resulted in the optimization
is a related optimization in MySQL and MariaDB. It uses inferred NOT NULL conditions to perform filtering (but not index access)
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE items (
price DECIMAL(8,2),
weight DECIMAL(8,2),
...
INDEX(weight)
);-- Find items that cost more than 1000 $currency_units per kg:
SET optimizer_switch='not_null_range_scan=ON';
EXPLAIN
SELECT * FROM items WHERE items.price > items.weight / 1000;weight IS NOT NULL+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
| 1 | SIMPLE | items | range | NULL | weight | 5 | NULL | 1 | Using where |
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+-- Find orders that were returned
SELECT * FROM current_orders AS O, order_returns AS RET
WHERE
O.return_id= RET.id;SET optimizer_switch='not_null_range_scan=ON';