Which one is faster view or subquery?

Question

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.

score 6 · Answer 1 · answered Jul 05 '11 at 15:58

6

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.

answered Jul 05 '11 at 15:58

Joel Coehoorn

399,467
113
570
794

I see. So basically speed wise they are the same? – Yasen Zhelev Jul 05 '11 at 15:59
Thank you Joel. I know about the different purposes of views and subqueries, but I wanted to know if there will be any speed gain if using one of them that the other. – Yasen Zhelev Jul 05 '11 at 16:07

Michael Mior · Accepted Answer · 2011-07-06T17:49:48.370

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 http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/

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

Which one is faster view or subquery?

2 Answers2