mysql 谓词,MySQL谓词推动

Is there any way to force MySQL to push a predicate into a view?

Example:

CREATE TABLE t1(

id INT(11) NOT NULL AUTO_INCREMENT

PRIMARY KEY (id)

);

CREATE VIEW v1

AS SELECT * FROM t1;

The query below will not use PRIMARY KEY index in MySQL:

SELECT *

FROM v1

WHERE id = 1

Instead it will select everything from t1, create a derived table and then filter it out for id = 1.

Is there any way to overcome this?

PS: my real life example is a little bit more complex than the one above, but for simplicity I used the example above

解决方案

Yes but you'd have to switch to the fully-compatible MariaDB 10.2.2

This is on by default and can be switched off using optimizer_switch:

SET GLOBAL optimizer_switch='condition_pushdown_for_derived=off'