8

I have a MySQL view defined using the below (JOIN statements omitted for brevity).

CREATE VIEW vw_example AS 
SELECT a, b, c FROM x, y, z

Over many repetitions SELECT a, b, c FROM x, y, z is 5 times faster than SELECT a, b, c FROM vw_example.

I'm looking to understand why this is and how to bring the SELECT FROM vw_example performance inline with the underlying SELECT FROM x, y, z.

Jobu
  • 590
  • 7
  • 18
  • This question can be related to this [link](http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query)! – pallav_rus Mar 30 '13 at 18:21
  • 1
    And to this as well . . . http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/. – Gordon Linoff Mar 30 '13 at 18:27
  • 1
    @pallav_rus thanks for the link but it appears to address how views can have better performance than their underlying query. Also, it applies to SQL Server w/ indexed views. I don't believe indexed views are available in MySQL, but would love to learn that they are. – Jobu Mar 30 '13 at 18:33
  • @GordonLinoff Thanks for the link. It looks closer to the issue. It could be due to my inexperience, but it doesn't appear to address the issue I'm having where the view is slower than its exact underlying query. Instead, it appears to relate to problems that can arise JOIN'ing views or the inefficiency of SELECT'ing FROM a VIEW while adding a WHERE clause instead of using the underlying query with the same WHERE clause. – Jobu Mar 30 '13 at 18:43
  • 1
    @Jobu: If you can, try [MariaDB](https://kb.askmonty.org/en/what-is-mariadb-53/) (a MySQL drop-in replacement) that has some improvements on the optimizer regarding Views. – ypercubeᵀᴹ Mar 30 '13 at 18:53
  • @ypercube Thanks for the suggestion, but unfortunately replacing our database server isn't an option at this time . – Jobu Mar 30 '13 at 19:01
  • Which version of MySQL are you running? And are those `x,y,z` base tables or views? – ypercubeᵀᴹ Mar 30 '13 at 19:03
  • @ypercube `x,y,z` are base tables, otherwise I could see how the link from @GordonLinoff is applicable. I'm running MySQL 5.1.63. – Jobu Mar 30 '13 at 19:07
  • Then apart form avoiding Views, you can only try to optimize your queries (check for missing indexes or possible rewritings). If a query is sped up by 10x (and accordingly the view), it will still be 5x slower but perhaps tolerable for your application. – ypercubeᵀᴹ Mar 30 '13 at 19:17
  • @ypercube, thanks. I have already spent time optimizing the query, but perhaps there's more I can do. I still would like an explanation for the observed behavior. – Jobu Mar 30 '13 at 19:29
  • What is the performance of `SELECT a, b, c FROM (SELECT a, b, c FROM x, y, z) t` ? Same as the view? – ypercubeᵀᴹ Mar 30 '13 at 19:58
  • @ypercube `SELECT a, b, c FROM (SELECT a, b, c FROM x, y, z) t` is as fast as `SELECT a, b, c FROM x, y, z` . . . 5 times faster than `SELECT a, b, c FROM vw_example` – Jobu Mar 30 '13 at 22:24
  • I don't think this should be closed as a duplicate since that relates to the special case of using DISTINCT, which was not involved here. – Jobu Dec 30 '22 at 01:47

3 Answers3

11

Although you don't show a DISTINCT in your example query, I recently discovered this to be the one single distinguishing factor in the performance difference between a query run directly, vs run as a view.

With SELECT DISTINCT on my query doing an INNER JOIN on a 726,000 row table and a 303,000 row table, joining ON 3 columns for which each table has an index, the direct query is running about 0.15-0.16s duration. When I use the VIEW created with the same query (and nothing else) the duration is about 142-145s or about 10^3 times as long.

Removing DISTINCT reduced both the query itself and the view it is based on to 0.016s--there is virtually no difference at all.

I can't help with understanding why--only with recognizing one cause in one particular case.

Kirk Fleming
  • 497
  • 5
  • 15
2

I am not sure, but maybe mysql is using tmp tables much better for one of the queries. Please adjust these settings and then try again:

tmp_table_size   100M
max_heap_table_size 128M
query_cache_limit  350M
query_cache_size 300M

Use set global before them so that you can set them on the fly, hope this might just work. If not then you might need to look into re writing the query.

sth
  • 222,467
  • 53
  • 283
  • 367
Masood Alam
  • 415
  • 2
  • 6
1

It's hard to be precise - the best way to investigate is to run EXPLAIN on both flavours of the query.

However, my suspicion is that the query cache is at the heart of this - re-running the same query will seed the query cache, and unless the underlying data changes, or the cache gets flushed, you're likely to benefit from the caching.

You'd expect to get that benefit when hitting the view, too, but caching is non-deterministic, and my guess is that, somehow, your queries against the view aren't benefiting from the caching.

If the EXPLAINs are identical, that would be the best explanation...

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • The MySQL Query Cache is disabled on this system. As debugging with #ypercube showed, it's not driven by the presence of a derived table. But I do think I'm onto something from the `EXPLAIN` statements showing something in the query that can't be optimized away when accessing the data via a view. – Jobu Mar 31 '13 at 23:57