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'