How we can enable index_condition_pushdown in MySQL..?

Question

I have optimizer_switch variable as

mysql> show variables like 'optimizer_switch';
+------------------+-------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                     |
+------------------+-------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
+------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now I wants to enable index_condition_pushdown so how i will do it.

I also wants to know how this variable help in query execution plan. How it speed up a query..?

Answer 1

Index_Condition_Pushdown is used for Queries with the range, ref, eq_ref, and ref_or_null access methods, i.e when there is a need to access full table rows.

Where as Index Condition Pushdown is enabled by default; it can be controlled with the optimizer_switch system variable by setting the index_condition_pushdown flag.

To Switch:

 SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

for more you could read here: http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

and http://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html