Mysql InnoDB views are becoming slow and Not Using Where Clause

Question

Our Views with 500K rows are becoming terrible since they can not use the Indexing on underlying tables. The where clause on View has no effect on view creation. Is this true that view is created before the Mysql Where clause is applied as in Sql server? Do we have a way to speed up Our view queries with Where clause and Joins?

One of the issues you have with the MySQL view is that MySQL does not "push" the predicate from the outer query down into the view query. here

select * from reportview_23 where id = 100;

What is your question? You seem to understand how views work in MySQL. — Gordon Linoff, Nov 06 '18 at 13:32
. . I don't believe so in MySQL, although the most recent versions may have improved optimization with views. — Gordon Linoff, Nov 06 '18 at 14:45

score 0 · Answer 1 · answered Feb 10 '22 at 18:29

0

You could get into some query limitation inside the view, that prevent view to merge the precidate... Like using LIMIT, DISTINCT, sub-query columns...

answered Feb 10 '22 at 18:29

ALTEMIR DE SOUSA SOARES

1

could you provide some code for that? – rikyeah Feb 16 '22 at 10:35

Mysql InnoDB views are becoming slow and Not Using Where Clause

1 Answers1