Index Condition Pushdown

Index Condition Pushdown è un'ottimizzazione che riguarda i metodi di accesso che fanno uso degli indici: range, ref, eq_ref, ref_or_null e Batched Key Access. L'idea è controllare la parte della condizione WHERE che si riferisce ai campi indice (chiamati Pushed Index Condition) appena effettuato l'accesso all'indice. Se la Pushed Index Condition non viene soddisfatta, non occorre leggere l'intero record.

A partire da MariaDB 5.3.3, la Index Condition Pushdown è on per default. Per disabilitarla, impostare il suo flag in optimizer_switch, in questo modo:

SET optimizer_switch='index_condition_pushdown=off'

Quando si usa la Index Condition Pushdown, EXPLAIN mostra "Using index condition":

MariaDB [test]> explain select * from tbl where key_col1 between 10 and 11 and key_col2 like '%foo%';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tbl   | range | key_col1      | key_col1 | 5       | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

The idea behind index condition pushdown

In disk-based storage engines, making an index lookup is done in two steps, like shown on the picture:

Index Condition Pushdown optimization tries to cut down the number of full record reads by checking whether index records satisfy part of the WHERE condition that can be checked for them:

How much speed will be gained depends on - How many records will be filtered out - How expensive it was to read them

The former depends on the query and the dataset. The latter is generally bigger when table records are on disk and/or are big, especially when they have blobs.

Example speedup

I used DBT-3 benchmark data, with scale factor=1. Since the benchmark defines very few indexes, we've added a multi-column index (index condition pushdown is usually useful with multi-column indexes: the first component(s) is what index access is done for, the subsequent have columns that we read and check conditions on).

alter table lineitem add index s_r (l_shipdate, l_receiptdate);

The query was to find big (l_quantity > 40) orders that were made in January 1993" that took more than 25 days to ship:

select count(*) from lineitem
where
  l_shipdate between '1993-01-01' and '1993-02-01' and
  datediff(l_receiptdate,l_shipdate) > 25 and
  l_quantity > 40;

EXPLAIN without Index Condition Pushdown:

-+----------+-------+----------------------+-----+---------+------+--------+-------------+
 | table    | type | possible_keys         | key | key_len | ref | rows    | Extra       |
-+----------+-------+----------------------+-----+---------+------+--------+-------------+
 | lineitem | range | s_r                  | s_r | 4       | NULL | 152064 | Using where |
-+----------+-------+----------------------+-----+---------+------+--------+-------------+

with Index Condition Pushdown:

-+-----------+-------+---------------+-----+---------+------+--------+------------------------------------+
 | table     | type | possible_keys | key | key_len | ref | rows     | Extra                              |
-+-----------+-------+---------------+-----+---------+------+--------+------------------------------------+
 | lineitem | range | s_r            | s_r | 4       | NULL | 152064 | Using index condition; Using where |
-+-----------+-------+---------------+-----+---------+------+--------+------------------------------------+

The speedup was:

• Cold buffer pool: from 5 min down to 1 min
• Hot buffer pool: from 0.19 sec down to 0.07 sec

Status variables

There are two server status variables

That way, the value Handler_icp_attempts - Handler_icp_match shows the number records that the server did not have to read because of Index Condition Pushdown.

See Also

• What is MariaDB 5.3
• Index Condition Pushdown in MySQL 5.6 manual (MariaDB's and MySQL 5.6's Index Condition Pushdown implementations have the same ancestry so are very similar to one another).