MySQL predicate promotion

Is there a way to get MySQL to push the predicate into the 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 the PRIMARY KEY index in MySQL:

SELECT *
FROM v1
WHERE id = 1

      

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

Is there a way to overcome this?

PS: my real life example is a little more complicated than the previous one, but for the sake of simplicity I used the example above

PPS: Here is a related question: How do I force MySQL to use an INDEX query to view?

+3


source to share


1 answer


Yes, but you will have to switch to fully compatible MariaDB 10.2.2

This is enabled by default and can be disabled with optimizer_switch

:



SET GLOBAL optimizer_switch='condition_pushdown_for_derived=off'

      

0


source







All Articles