Condition pushdown

Condition pushdown is not a new kind of wrestling move but a special optimization in the MySQL server. It allows the Storage Engine and Information Schema plugins to use the WHERE condition to filter the rows returned to MySQL, as opposed to letting MySQL process the WHERE condition internally after the rows have been received. So it is effectively pushing down the WHERE condition into the plugin. This optimization is not always meaningful to use. However, if the total number of rows is large, while the number of rows that satisfy the WHERE clause is small, and generating rows is relatively expensive—in such a case this optimization can bring huge performance benefits. For example, the INFORMATION_SCHEMA.COLUMNS table uses it, because it needs to open a table to get the information about its columns, and it is far better to open only one table that the user is interested in, than to open every table in every database, potentially thousands of them.

In these...