Search code examples
mysqlindexing

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.

Solution

  • 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 for a given table.

    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.