The Ultimate Guide to High Availability with MariaDB

Index Condition Pushdown

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

Index Condition Pushdown is an optimization that is applied for access methods that access table data through indexes: range, ref, eq_ref, ref_or_null, and Batched Key Access. The idea is to check part of the WHERE condition that refers to index fields (we call it Pushed Index Condition) as soon as we've accessed the index. If the Pushed Index Condition is not satisfied, we won't need to read the whole table record.

Index Condition Pushdown is of by default. To enable it, set its optimizer_switch flag like so:

SET optimizer_switch='index_condition_pushdown=on'

When Index Condition Pushdown is used, EXPLAIN will show "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-access-2phases

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:

index-access-with-icp

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

More info

  • 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).

See Also:

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.