3

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

PPS: here's a related Stack Overflow question: How do I get MySQL to use an INDEX for view query?

Community
  • 1
  • 1
Razvan
  • 2,436
  • 18
  • 23
  • It also does not seem to be supported for subqueries. – crokusek Oct 03 '14 at 08:08
  • The Entity Framework (MSVS 2014) generates queries where the from clause is actually a subquery and predicate pushing does not happen for this form: select * from (select * from someView) sv where x = x; – crokusek Nov 17 '14 at 19:18

1 Answers1

0

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'
architectonic
  • 2,871
  • 2
  • 21
  • 35