ANALYZE: Interpreting rows and filtered members

This article describes how to interpret r_rows and r_filtered members in ANALYZE FORMAT=JSON when an index-based access method is used.

Index-based access method

Index-based access method may employ some or all of the following:

• attached_condition checking

Consider a table access which does all three:

The access is performed as follows:

Access diagram

Statistics values in MariaDB before 11.5

In MariaDB versions before 11.5, the counters were counted as follows:

that is,

• r_rows is counted after Index Condition Pushdown check and Rowid Filter check.

• r_filtered only counts selectivity of the attached_condition.

• selectivity of the Rowid Filter is in rowid_filter.r_selectivity_pct.

Statistics values in and later versions

Starting from (), the row counters are:

• r_index_rows counts the number of enumerated index tuples, before any checks are made

• r_rows is the same as before - number of rows after index checks.

The selectivity counters are:

• r_icp_filtered is the percentage of records left after pushed index condition check.

• rowid_filter.r_selectivity_pct shows selectivity of Rowid Filter, as before.

• r_filtered is the selectivity of attached_condition check, as before.

ANALYZE output members

in ANALYZE FORMAT=JSON output these members are placed as follows:

Whenever applicable, r_index_rows is shown. It is comparable with rows - both are numbers of rows to enumerate before any filtering is done. If r_index_rows is not shown, r_rows shows the number of records enumerated.

Then, filtering members:

filtered is comparable with r_total_filtered: both show total amount of filtering.

ICP and its observed filtering. The optimizer doesn't compute an estimate for this currently.

attached_condition and its observed filtering.

_{This page is licensed: CC BY-SA / Gnu FDL}