Question 403784 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – can a sql sentence use index covering and index condition pushdown simultaneously？

When the query does not meet the conditions for index coverage,if Innodb will use index condition pushdown by auto?
or how to explain these two thing by simple word or example.

Tags:

2

Answers


  1. 0

    Index coverage: The index can fully satisfy the query without accessing the table data.

    Index condition pushdown (ICP): The database engine pushes down certain conditions to the storage engine when there’s no full index coverage, to optimize the query.

    In other words, index coverage is about the index’s capability, while ICP is a technique the database uses to optimize queries when there’s no full index coverage.

    Login or Signup to reply.
  2. 0

    https://dev.mysql.com/doc/refman/en/index-condition-pushdown-optimization.html says:

    ICP is used for the range, ref, eq_ref, and ref_or_null access methods when there is a need to access full table rows.

    And later in the same manual page:

    EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.

    (bold emphasis mine)

    This means that ICP is mutually exclusive with covering index optimization. No query will use both optimizations.

    Both optimizations are employed automatically, if the optimizer can find a way to use them. In some queries, these optimizations are not applicable.

    A covering index is used when all the columns for a given table referenced in the query come from a single index. In that case, the full rows referenced by the index don’t need to be read at all.

    ICP is used to avoid reading some full rows, by applying conditions to columns at the index level.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search