WL#5639: Enable Index Condition Pushdown optimizer feature
Affects: Server-5.6 — Status: Complete — Priority: Medium
The optimizer feature named Index Condition Pushdown (ICP) has been implemented as part of WL#2474/WL#2475. The feature is currently in our source code but disabled when doing a normal build. In order to enable it you have to include a special pre-processor define either by including it on the command line or by editing the source. This purpose of this worklog is to: 1. enable Index Condition Pushdown in the server so that it will be used by the MySQL optimizer 2. enable the index_condition_pushdown switch to be configurable via the optimzer_switch configuration variable. The default value for index_condition_pushdown will be on.
The Index Condition Pushdown (ICP) has been implemented as part of WL#2474/WL#2475 and is included in our source code but is not enabled. The goal of this worklog is to enable index condition pushdown so that it will be used by the MySQL optimizer and allow users to control whether it should be used or not. The following changes to MySQL will be implemented: 1. Enable index condition pushdown With this feature enabled the optimizer will consider to push down to the handler/storage engine parts (or all) of the where condition that can be evaluated by the handler/storage engine using the content of the index that is used for execution of the query. The main change by enabling index condition pushdown should be improved performance for certain queries. The user should be able to see if index condition pushdown has been used by looking in the "Extra" column of the EXPLAIN output. If this field contains the string "Using index condition" parts or all of the where statement has been pushed down and will be evaluated by the handler/storage engine. 2. Enable the user to control the use of index condition pushdown The code for controlling whether index condition pushdown should be used or not is already in the server code (see WL#5116) but is also disabled. When this has been enabled the user will be able to control the use of index condition pushdown by using the optimizer_switch configuration variable (see http://dev.mysql.com/doc/refman/5.5/en/switchable-optimizations.html). The new optimizer_switch flag that will become available is named "index_condition_pushdown" and can take the values "on", "off" and "default". The default value for this optimizer switch flag will be "on".
The main changes that are needed in order to make Index condition pushdown being enabled in default builds is the following: 1. In sql/sql_priv.h: Include the OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN flag in the OPTIMIZER_SWITCH_DEFAULT macro also for the case where OPTIMIZER_SWITCH_ALL is undefined. 2. In sql/sys_vars.cc: - Change *optimizer_switch_names variable so that "index_condition_pushdown" is included also when OPTIMIZER_SWITCH_ALL is not defined. - Adjust text written by --help 3. Adjust test result files.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.