Re-ran benchmark on the smaller query.

Top CPU consumers when ICP=ON:

    18.45%   mysqld  mysqld              [.] btr_search_guess_on_hash(dict_index_t*, btr_search_t*, dtuple_t const*, unsigned long, unsigned long, btr_cur_t*, unsigned long, mtr_t*)

    18.43%   mysqld  mysqld              [.] rec_get_offsets_func(unsigned char const*, dict_index_t const*, unsigned long*, unsigned long, mem_block_info_t**)

     8.08%   mysqld  mysqld              [.] mtr_memo_slot_release_func(mtr_memo_slot_t*)

     7.12%   mysqld  mysqld              [.] buf_page_get_known_nowait(unsigned long, buf_block_t*, unsigned long, char const*, unsigned long, mtr_t*)

     5.44%   mysqld  mysqld              [.] buf_page_optimistic_get(unsigned long, buf_block_t*, unsigned long, char const*, unsigned long, mtr_t*)

     4.52%   mysqld  mysqld              [.] row_search_for_mysql(unsigned char*, unsigned long, row_prebuilt_t*, unsigned long, unsigned long)

Top CPU consumers when ICP=OFF:

    29.21%   mysqld  mysqld              [.] row_sel_enqueue_cache_row_for_mysql(unsigned char*, row_prebuilt_t*)

    11.75%   mysqld  mysqld              [.] rec_get_offsets_func(unsigned char const*, dict_index_t const*, unsigned long*, unsigned long, mem_block_info_t**)

     5.37%   mysqld  mysqld              [.] mtr_memo_slot_release_func(mtr_memo_slot_t*)

     3.98%   mysqld  mysqld              [.] buf_page_get_known_nowait(unsigned long, buf_block_t*, unsigned long, char const*, unsigned long, mtr_t*)

     3.57%   mysqld  mysqld              [.] buf_page_optimistic_get(unsigned long, buf_block_t*, unsigned long, char const*, unsigned long, mtr_t*)

     2.79%   mysqld  mysqld              [.] row_search_for_mysql(unsigned char*, unsigned long, row_prebuilt_t*, unsigned long, unsigned long)

Again, the same picture, but ICP=ON has the new biggest user: row_sel_enqueue_cache_row_for_mysql

Is this really a bug? In my understanding there is no certainty that ICP would help on all cases, When ICP is used, row_search_for_mysql() needs to check ICP condition before putting the row into cache. ICP condition can only be checked when index fields are in table->record[0].
The solution is to unpack into table->record[0], make ICP check, and then put the record into cache with memcpy. All this takes time.

It seems that when the condition selectivity is low (most of the rows match), ICP would add more overhead than it's worth.

It is technically possible to estimate the selectivity of the ICP conidtion. But how to estimate the ICP overhead?

In theory, Index_condition_pushdown should almost always be equal or faster. The more rows that does not match the condition, the bigger the difference. Even if all rows matches the condition, the difference should be practically neglectable in theory. (more of this below).

The big benefits with index_condition_push_down

• We check the condition earlier in the engine code, so that we don't have to go up to the sql level and check the row there (there is a lot of code executed when going in and out from the engine).
• We can avoid reading the row if the condition does not match (if it is not an clustered index)

If there is even a few % of the rows that matches the condition, Index_condition_pushdown should perform better than not having it enabled.

The extra overhead for index_condition_pushdown are:

• Extra call to check index_condition_pushdown (and code executed in it)
• Copying a key to the row buffer
• In case of InnoDB, therte is an internal row cache that causes extra overhead (more data to copy). The question is how to avoid this one.

To answer Sergei Golubchik, the above is not hard to estimate.
We already estimate things for handler_rowid_filter_checks.
However, a better route would be to minimise the overhead for ICP.

Checking the index_condition should be neglect able as this is the same check we have to in the
SQL level anyway.

Reasons why index_condition_pushdown is slower:

• The index_condition_pushdown check code was not that optimal in 10.11. It's faster (but not yet perfect) in 11.0.
• We call thd_kill_level() for every checked row, which adds overhead. This can be avoided for most calls.
• InnoDB row cache overhead in case of ICP.
• For some complex WHERE clauses the ICP condition may be executed twice. This happens for example with WHERE (key=1 and b=c) or (key=1 and d=e), in which case:
  ICP: key=1 OR key=1
  WHERE: (key=1 and b=c) or (key=1 and d=e)