Range Optimizer

Range optimizer is a part of MariaDB (and MySQL) optimizer that takes as input

• the table and index definition(s)
• the WHERE condition (or ON expression if the table is inner in an outer join)

and constructs list of ranges one can scan in an index to read the rows that match the WHERE condition, or a superset of it. It can also construct an "index_merge" plan, where one needs to ranges from several indexes and compute a union and/or intersection.

Basic example

Consider a table

create table t1 (
  key1 int,
  key2 varchar(100),
  ...
  index(key1),
  index(key2)
);

and query

-- Turn on optimizer trace so we can see the ranges:
set optimizer_trace=1; 
explain select * from t1 where key1<10 and key2='foo';
select * from information_schema.optimizer_trace\G

This shows the ranges that the optimizer was able to infer:

                    "range_scan_alternatives": [
                      {
                        "index": "key1",
                        "ranges": ["(NULL) < (key1) < (10)"],
                        ...
                      },
                      {
                        "index": "key2",
                        "ranges": ["(foo) <= (key2) <= (foo)"],
                        ...
                      }
                    ],

Ranges are non-overlapping

Ranges for multi-part indexes