Rowid filter: a sorted key pre-filter before table accessMariaDB 11.4

See how MariaDB scans a filtering index to collect the matching primary keys, then skips the row read for entries not among them.

Lesson familyIndex access

Parameters (Rowid Filter)

About this example
Ready: press Next step, or Play to run it all
0.0s 0.0s

About this example

Example query this animation executes

-- idx_status on (status), idx_date on (created_date)
-- Main access via idx_date, rowid filter from idx_status
SELECT * FROM orders
WHERE  created_date > '2024-01-01'
AND    status = 'shipped';

MariaDB scans idx_status first and keeps the shipped orders' primary keys in a sorted array, then uses idx_date for the main scan — skipping the clustered index read for every entry whose key is not in the array.

What you'll see in the animation

  • Step 1 reads only the 'shipped' entries of idx_status and puts each one's primary key (id) into the rowid filter: the blue slots at the top, a sorted array.
  • Step 2 reads idx_date entries one by one. Each entry holds a date and an id; the order's status is only in the row.
  • For each entry InnoDB looks its id up in the filter (binary search). Found: the row is read from the clustered index (green check).
  • Not found: the entry is skipped and the row is never read (red cross), which saves one clustered index lookup.
  • Next step shows one entry at a time, and the simplified code beside the drawing highlights the lines each step runs.

Cost readout (Rowid Filter)

Main index rows ?Entries returned by the main index scan (idx_date). Without a rowid filter each one costs a clustered index read.

—

Filter selectivity ?Percentage of idx_date entries whose primary key is in the filter (status = 'shipped'). Lower is better for the filter.

—

Rows after filter ?Entries whose primary key the filter holds. Only these are read from the clustered index.

—

Rows skipped ?Entries whose primary key is not in the filter. Each skip avoids one clustered index lookup.

—

Table fetches (without filter) ?Without a rowid filter, every idx_date entry costs a clustered index read.

—

Table fetches (with filter) ?With the rowid filter, only entries found in it cost a clustered index read.

—

I/O saved ?Clustered index reads saved by skipping entries not in the filter. Equal to rows skipped.

—

Table fetches vs main index rows (log–log, selectivity fixed)

Learn more — when does MariaDB use rowid filter?

Rowid filtering was introduced in MariaDB 10.4 and is enabled by default (optimizer_switch='rowid_filter=on'). This optimisation is not available in MySQL.

The optimizer considers rowid filter when:

1. The query accesses a table through one index (the main access path) but another index could filter out a large fraction of rows before the expensive table-row fetch.

2. The filtering index is selective enough that building the in-memory filter and checking it per entry is cheaper than reading every row from the table.

3. The filter fits in memory. It is a sorted array of the qualifying primary keys, at most max_rowid_filter_size bytes (128 KiB by default), so it suits a filter index that matches few rows.

Rowid filter works best when the filter index is very selective (few rows match) but the main index returns many rows. The more entries the filter can eliminate, the more clustered index reads are saved. The check happens inside InnoDB, before the row is read, right after any pushed index condition.

In EXPLAIN output the type column shows range|filter (or ref|filter), the key column idx_date|idx_status, and the Extra column Using rowid filter.

Sources: MariaDB Knowledge Base “Rowid Filtering Optimization”; MariaDB Server 10.4 Release Notes.