Description:
In 6.0, behaviour and applicability of engine_condition_pushdown has changed. Docs need to be updated to reflect this.
Quoting Paul's recent emails on the subject:
In 6.0, this system variable now also applies to the Index Condition
Pushdown optimization strategy, which can be used with MyISAM tables. That
means this variable no longer is NDB-only. Do you think it would make sense
to move its description from mysql-cluster-optvar-core.xml to dba-core.xml?
[and]
Two questions about the description of condition pushdown in the
manual. (Currently this is at
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html but it will be
moving soon.)
> Using where with pushed condition
> This item applies to NDBCLUSTER tables only. It means that MySQL
> Cluster is using condition pushdown to improve the efficiency of a
> direct comparison between a non-indexed column and a constant. In
> such cases, the condition is “pushed down” to the cluster's data
> nodes where it is evaluated in all partitions simultaneously. This
> eliminates the need to send non-matching rows over the network, and
> can speed up such queries by a factor of 5 to 10 times over cases
> where condition pushdown could be but is not used.
What does "partition" mean in this context?
...
> Condition pushdown cannot be used with either of these two queries:
> SELECT a,b FROM t1 WHERE a = 10;
> SELECT a,b FROM t1 WHERE b + 1 = 10;
> With regard to the first of these two queries, condition pushdown
> is not applicable because an index exists on column a. In the case
> of the second query, a condition pushdown cannot be employed because
> the comparison involving the non-indexed column b is an indirect
> one. (However, it would apply if you were to reduce b + 1 = 10 to
> b = 9 in the WHERE clause.)
For the "With regard to" sentence, is the reason that condition
pushdown isn't applicable because an index access method would be
more efficient and thus would be chosen in preference to condition
pushdown?
How to repeat:
See Description.
Suggested fix:
Answer Paul's questions, make necessaary changes/moves.
Description: In 6.0, behaviour and applicability of engine_condition_pushdown has changed. Docs need to be updated to reflect this. Quoting Paul's recent emails on the subject: In 6.0, this system variable now also applies to the Index Condition Pushdown optimization strategy, which can be used with MyISAM tables. That means this variable no longer is NDB-only. Do you think it would make sense to move its description from mysql-cluster-optvar-core.xml to dba-core.xml? [and] Two questions about the description of condition pushdown in the manual. (Currently this is at http://dev.mysql.com/doc/refman/5.0/en/using-explain.html but it will be moving soon.) > Using where with pushed condition > This item applies to NDBCLUSTER tables only. It means that MySQL > Cluster is using condition pushdown to improve the efficiency of a > direct comparison between a non-indexed column and a constant. In > such cases, the condition is “pushed down” to the cluster's data > nodes where it is evaluated in all partitions simultaneously. This > eliminates the need to send non-matching rows over the network, and > can speed up such queries by a factor of 5 to 10 times over cases > where condition pushdown could be but is not used. What does "partition" mean in this context? ... > Condition pushdown cannot be used with either of these two queries: > SELECT a,b FROM t1 WHERE a = 10; > SELECT a,b FROM t1 WHERE b + 1 = 10; > With regard to the first of these two queries, condition pushdown > is not applicable because an index exists on column a. In the case > of the second query, a condition pushdown cannot be employed because > the comparison involving the non-indexed column b is an indirect > one. (However, it would apply if you were to reduce b + 1 = 10 to > b = 9 in the WHERE clause.) For the "With regard to" sentence, is the reason that condition pushdown isn't applicable because an index access method would be more efficient and thus would be chosen in preference to condition pushdown? How to repeat: See Description. Suggested fix: Answer Paul's questions, make necessaary changes/moves.