not_null_range_scan optimization

You are viewing an old version of this article. View the current version here.

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.

A basic (but a bit artificial) example:

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;

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

weight IS NOT NULL

and pass it to the range optimizer. Range optimizer can, in turn, evaluate whether it makes sense to construct range access from the condition:

+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|    1 | SIMPLE      | items | range | NULL          | weight | 5       | NULL | 1    | Using where |
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+

A more complex example with a join. This is fairly common when using ORMs:

select * from current_orders as O, order_returns as RET
where 
  O.return_id= RET.id;

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.

Controlling the optimization

The optimization is not enabled by default. One can enable it like so

 set optimizer_switch='not_null_range_scan=on';

See also

  • MDEV-15777 - Jira bug report which resulted in the optimization
  • NULL Filtering Optimization is a related optimization in MySQL and MariaDB. It uses inferred NOT NULL conditions to perform filtering (but not index access)

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.