CODEWITHSUNDEEP
mysqlsqlviewsubquery
Yasen Zhelev
Yasen Zhelev

Reputation: 4045

Which one is faster view or subquery?

The question says it all. Which one is faster? And when should we use view instead subquery and vice verse when it comes to speed optimisation?

I do not have a certain situation but was thinking about that while trying some stuff with views in mysql.

Upvotes: 5

Views: 5095

Answers (2)

Michael Mior
Michael Mior

Reputation: 28753

Neither are particularly efficient in MySQL. In any case, MySQL does no caching of data in views, so the view simply adds another step in query execution. This makes views slower than subqueries. Check out this blog post for some extra info.

One possible alternative (if you can deal with slightly outdated data) is materialized views. Check out Flexviews for more info and an implementation.

Upvotes: 5

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

A smart optimizer will come up with the same execution plan either way. But if there were to be a difference, it would be because the optimizer was for some reason not able to correctly predict how the view would behave, meaning a subquery might, in some circumstances, have an edge.

But that's beside the point; this is a correctness issue. Views and subquerys serve different purposes. You use views to provide code re-use or security. Reaching for a subquery when you should use a view without understanding the security and maintenance implications is folly. Correctness trumps performance.

Upvotes: 6

Related Questions