Take the 2-minute tour ×

I have a complex query using many joins (8 in fact). I was thinking of simplifying it into a view. After a little research I can see the benefits in simplicity and security. But I didn't see any mentions of speed.

Do views work like prepared statements, where the query is pre-compiled? Is there any notable performance gain from using views?

share|improve this question
Some databases will pre-compile the view, but I don't believe MySQL will. You may want to look at this question: stackoverflow.com/questions/1021319/how-to-optimize-mysql-views –  James Black Jan 25 '11 at 2:17
add comment

5 Answers

up vote 6 down vote accepted

No, a view is simply a stored text query. You can apply WHERE and ORDER against it, the execution plan will be calculated with those clauses taken into consideration.

share|improve this answer
This isn't strictly true. It is essentially correct if you choose merge for your view algorithm, but if you choose temptable it will materialize. Undefined (the default, if unspecified) lets MySQL choose, which can do something you might not expect. –  Ray Jan 25 '11 at 2:18
I am no MySQL specialist, but I'm pretty sure that the TempTable algorithm controls how MySQL processes the view when results are requested and has no effect at all on how the view is stored. –  Larry Lustig Jan 25 '11 at 2:38
Yes, which is quite different from just having a piece of text that gets executed as if it were an ad-hoc query. –  Ray Jan 25 '11 at 2:46
Perhaps you know better than I do, but it appears to me yes, the view is stored text that gets interpreted at the time its results are requested. TempTable simply tells MySQL to perform that evaluation, at runtime, using a temporary table rather than trying to modify the SQL of the original query to include the view definition. There is no pre-computation or analysis of the contents of the view SQL at view creation time. –  Larry Lustig Jan 25 '11 at 2:52
add comment

You're forgetting the most important performance related aspect of views: it can cache your results. This will make MySQL pull the results from a buffer and not access the hard disk to run the query (http://goo.gl/3xCce).

Of course if you will be sorting and other stuff that needs indexing it will do more harm than good, but if you plan to make the same query repeatedly you can gain a lot of performance (specially if it has JOINs)

See more info: http://www.dbtuna.com/article.php?id=29 http://kb.mediatemple.net/questions/258/Basic+MySQL+performance+tuning+%28MySQLd%29#dv http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

share|improve this answer
Not true. VIEWs are not materialized and cached. Each query execution re-runs the VIEW too. All those links you provide do not even mention the word VIEW. –  Jannes Oct 23 '13 at 15:37
add comment

A view's basically a stored subquery. There's essentially no difference between:

FROM someview


    SELECT somestuff 
    FROM underlying table

except the view's a bit more portable, as you don't have to write out the underlying query each time you want to work with whatever data it returns.

share|improve this answer
And what if MySql chooses a temptable view algorithm? –  Ray Jan 25 '11 at 2:20
I completely disagree. a MySQL VIEW is not just an alias for a stored string of a query. You can have huge performances gains or losses if you use it correctly or not. See my answer for more details. –  Juan Nov 14 '11 at 18:25
add comment

It can sometimes help, but it's no silver bullet. I've seen a view help performance, but I've also seen it hurt it. A view can force materialization, which can sometimes get you a better access path if MySQL isn't choosing a good one for you.

share|improve this answer
MySQL doesn't support materialized views. So I fail to see how it could help performance over the equivalent query... –  ircmaxell Jan 25 '11 at 2:19
They don't call it materialized views, but "temptable" is one of the view algorithms, which does roughly as the name implies –  Ray Jan 25 '11 at 2:36
I've looked this up and the temptable algorithm is definitely not a version of materialized views. To date, there is no support for materialized views, or any form of pre-solved views, in MySQL (unless you manually project a view to a table on a periodic basis). –  Larry Lustig Jan 25 '11 at 2:59
add comment

Using MySQL In a simple view vs query comparison it seems that views do perform a bit faster ...

Query :: Showing rows 0 - 29 ( 2,167,134 total, in query, Query took 0.0206 sec)

 FROM db_usa
 WHERE tbl_state
 IN (
 LIMIT 0 , 30

Using the same query as a view ...

View :: Showing rows 0 - 29 ( 2,167,134 total, in query, Query took 0.0203 sec)

FROM View_OB_State_ASC
LIMIT 0 , 30
share|improve this answer
Do you seriously think 0.0003s is a remotely significant improvement? –  DisgruntledGoat Aug 11 '12 at 23:15
@downvote ... Not really hence I said seems a bit and not a lot faster ... thanks for the downvote. –  Eddie B Aug 12 '12 at 6:28
Sorry mate, but a 0.0003s difference is not conclusive at all. Try running your code a few more times, you'll see the time fluctuate. Also, this question is well over a year old and has a selected answer. –  DisgruntledGoat Aug 12 '12 at 10:24
add comment

Your Answer


