36

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?

DisgruntledGoat
  • 70,219
  • 68
  • 205
  • 290
  • 2
    Some databases will pre-compile the view, but I don't believe MySQL will. You may want to look at this question: http://stackoverflow.com/questions/1021319/how-to-optimize-mysql-views – James Black Jan 25 '11 at 02:17
  • 3
    Contrary to the answers - In my experience, for views with lots of joins, doing a direct query runs faster. – Joshua Kissoon Nov 20 '15 at 17:23
  • 1
    I agree with Joshua. I was told during MySQL certification that views run faster (and made logic), but in experience, I see that direct queries for complex queries with several joins work faster than making a query to a view with itsown joins – Vianick Oliveri Dec 20 '19 at 19:24

3 Answers3

20

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.

Gottlieb Notschnabel
  • 9,408
  • 18
  • 74
  • 116
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • 5
    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 02:18
  • 1
    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 02: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 02:46
  • 2
    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 02:52
  • This answer is not strictly true. You should definitely look at accepted answer in this question: https://dba.stackexchange.com/questions/16372/when-to-use-views-in-mysql – lukyer May 14 '15 at 11:13
  • 1
    @lukyer: I've read the answer you linked, and there is no disagreement between the information in that answer and my statement. MySQL has a couple of different ways of evaluating views but none of them involve maintaining a solved version of the data query. In neither case will the view provide a performance benefit over using the raw query. – Larry Lustig May 14 '15 at 15:31
2

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

SELECT *
FROM someview

and

SELECT *
FROM (
    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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 1
    And what if MySql chooses a temptable view algorithm? – Ray Jan 25 '11 at 02:20
  • 2
    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 Ignacio Nov 14 '11 at 18:25
0

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.

Ray
  • 4,829
  • 4
  • 28
  • 55
  • 1
    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 02: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 02:36
  • 2
    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 02:59