0

I have created a view on a simple table. My problem is that my average execution time of a select on that view is about 29 seconds. However, if I run the select statement which describes the view directly, the query executes in about 0.015 seconds.

Now, I have looked up some info, and here and here, people basically say that it should be roughly the same since a view is just a stored query.

Is it possible that I have this much of a difference in time? I have tried using SQL_NO_CACHE to make sure no cache is used so I get representative data when testing both options.

I would prefer to keep my view unless I have no option in reducing costs.

Community
  • 1
  • 1
Valentin Grégoire
  • 1,110
  • 2
  • 12
  • 29

1 Answers1

0

After a lot of research and trial and error I have concluded that on even simple queries and views, the performance can be a huge difference when selecting * from a view or just running the select query that is described in the creation of the view.

Valentin Grégoire
  • 1,110
  • 2
  • 12
  • 29
  • Where did your research lead in terms of documentation, blogs, or other answers? – Drew Jul 18 '16 at 13:34
  • @Drew, other questions on SE through some Google searches. And of course a lot of trial and error :). – Valentin Grégoire Jul 18 '16 at 14:36
  • Ok, can you share some stat at least. Just trying to make this a question we don't delete or find useless. – Drew Jul 18 '16 at 14:37
  • 1
    @Drew, I've mentioned my sources in the question itself. Also, I asked a question here previously (http://stackoverflow.com/questions/38433003/performance-on-view-in-mysql/38441841#38441841). One of the answers state that if I query my view with a where clause, my execution time can differ from executing the raw select statement which the view is described by. – Valentin Grégoire Jul 19 '16 at 09:46