MySQL VIEW as Performance Troublemaker

I start to see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the queries – to keep queries simple without really thinking how it affects server performance.

Even worse than that – looking at the short table which just gets single row from the table by the key we think this is a simple query, while can be a real monster instead with complexity hidden away in VIEW definition.

Just the other day I worked on optimizing application which uses VIEWs and was looking at the long-running query which just joined 2 tables… I ran EXPLAIN for it and got 200 of rows in the result set just for explaining due to several layers of cascaded views built on top of one another so it is easy to write the queries, some of them it turn used subqueries subselects and derived tables.

It is also very dangerous if you assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts.

MySQL has two ways of handling the VIEWS

Query merge, in which case VIEW is simply expanded as a macro or Temporary Table in which case VIEW is materialized to temporary tables (without indexes !) which is later used further in query execution.

There does not seem to be any optimizations applied to the query used for temporary table creation from the outer query and plus if you use more then one Temporary Tables views which you join together you may have serious issues because such tables do not get any indexes.

Let me now show a couple of examples.

Assume we have the comments table which holds users comments to the blog, naturally containing user_id which left comment, comment_id and comment text:

So how would you get a number of comments left by the given user?

So how would we solve the same problem having things more modular and using MySQL VIEWs?

So we create the view which gives us back counts for each user and can simply query from that table restricting by user_id.
If this would be handled properly inside MySQL there would be even good reason to do that – so later you can change your application and convert user_count to summary table avoid changing any queries directly. Unfortunately, it does not work.

It is interesting to see EXPLAIN for such query and time for the query which fetches everything from the VIEW – it is almost the same as getting only one row, and note even EXPLAIN takes the same amount of time:

So now let’s create a very artificial query which will JOIN 2 views just to see how indexes are used:

As you can see we get 2 derived tables in which case are fully populated and “full join” used to join between them. In this particular case it is not that bad because “join cache” is used to perform it relatively efficient, however for large derived tables it will become a nightmare.

So be very careful implementing MySQL VIEWs in your application, especially ones which require temporary table execution method. VIEWs can be used with very small performance overhead but only in case, they are used with caution.

MySQL has long way to go getting queries with VIEWs properly optimized.

Share this post

Comments (62)

  • KaufKamagraJei

    To Raka: I didn’t face any problem…what type of server did you use? what configuration? Probably the reason is in config files…

    August 12, 2007 at 12:00 am
  • Free Online Love

    i get an error if i use views for my database but i guess its just because it is very big and my server is too slow have to move to new one. be careful with big data amounts

    August 12, 2007 at 12:00 am
  • Cialis McEllister

    i trie the code from shelley but somehow i get an error when i try to use the Merge in my database. i already tried it several times but i cant find the error. are you sure that the code is ok?

    August 12, 2007 at 12:00 am
  • Reise

    thanks for the post .. is helpfull .. also at shelley .. i have this needed for my work.

    August 12, 2007 at 12:00 am
  • Kamagra

    The code works 100% correct!! I don’t know why everybody got that problems…

    August 12, 2007 at 12:00 am
  • shelley

    The complete syntax of the CREATE VIEW command looks like this:

    VIEW name [(columnlist)] AS select command

    ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED.

    By default, MySQL always uses UNDEFINED, if no ALGORITHM clause is present (can be determined via SHOW CREATE TABLE viewname).

    Mysql prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient.

    // ***Views have slow performance on tables with large amount of data

    August 12, 2007 at 12:00 am
  • Xaprb

    Hi Peter,

    Good article as always. Your second code example has some text comments in it that don’t line-wrap and are hard to read.

    August 12, 2007 at 4:56 pm
  • Mike Willbanks

    I actually had previously utilized MySQL’s VIEWs in a project when they had just came out. I figured that they would actually optimize how other databases would however what I came to find was that it was one of the worst performance bottlenecks as my data set grew. What I came to find was that MySQL would join all of the data together first and then it would run through the search criteria.

    August 13, 2007 at 3:25 pm
  • Priya Raman

    We recently converted from Access to MySQL. In this transition, lot of queries were written as views in MySQL. The performance was so bad that I decided to rewrite all of them using the base tables directly. As you aptly said, MySQL does have a long way to go as far as views are concerned.

    August 15, 2007 at 1:32 pm
  • ChrisK

    I think the problem is not the VIEWs themselves, but how one attempted to use it.

    Putting an aggregate function into a VIEW seemed like a bad choice to begin with (especially one that forced a table scan for each use), and that bad initial choice is compounded if one plans on using it with any frequency. At that cost, you may as well have just written a trigger on the comments table to update a user_counts table (or re-generate it entirely using the query you used for your view) for each insert or delete. You could read the now summarized user_count table (that only regenerates when needed) with the benefits of an index to get a specific user’s posting count.

    You do point out a good thing, and that is that people need to really think through what they are trying to ultimately get to and figure out a way to get that data as efficiently as possible. Just using a mechanism (VIEWs) simply because they are there and available to use does not make them necessarily a good choice. Also, don’t put queries into a VIEW that you would not deem efficient to run normally.

    September 6, 2007 at 12:37 pm
  • peter


    It is both. MySQL Views could be optimized better. You can use MySQL Views wisely only in cases when they are optimized well.

    Many people do not think about performance until it starts to hurt badly so they just write queries (and put them in the views) in a way it gets them info they want easiest way.

    September 7, 2007 at 3:01 pm
  • Jim

    “Many people do not think about performance until it starts to hurt badly so they just write queries (and put them in the views) in a way it gets them info they want easiest way.”

    That could be construed as a feature, not a bug. You know what they say about premature optimization.

    November 15, 2007 at 3:54 pm
  • peter

    Well… Not optimizing beyond the need is one thing. Thinking you never would need to optimize is completely different.

    November 15, 2007 at 4:23 pm
  • Bill Ford

    If I have a view used to join data (via a UNION select) stored in separate tables, then use a where clause when selecting from the view, is that a case where MySQL would be inefficient? I don’t want it to create a temporary table of ALL the data in ALL the tables, I really was hoping for more of a macro-expand kind of thing.

    November 20, 2007 at 9:48 am
  • Serban

    i see VIEWS like a good alternative to caching some of the queries. Example: you have a site, with a categories menu (parent->childs) that are conditioned to appear in the menu by the number of products they reffer to. Every time a user loads a page you load that menu. If you have a large database, you don’t want to execute each time that menu (query), so you only SELECT the view. i think this is a primary use of VIEWS besides caching reports that don’t contain indexes but full values. please correct me if i’m wrong.

    January 12, 2008 at 3:12 am
  • peter

    How would you use views to cache queries in MySQL ?

    MySQL does not have materialized views so whenever you access view it will be always reevaluated.

    January 22, 2008 at 4:47 am
  • Ascanio

    I’ve been working with MySQL Views since release 5 was first made available.
    It took me and my team about 2 months of testing to understand that it is just too soon to use Views.

    What we did as a solution was to create an extra layer between PHP and MySQL (written in PHP). Hardcoding the complex queries in functions, with our own caching logic. This made it for us, for now.

    February 9, 2008 at 1:53 pm
  • Geoff

    Have MySQL views improved at all – for any version?

    March 18, 2008 at 8:25 pm
  • peter

    Not in 5.1 at least. 6.0 have some optimizations which would affect views but I’m not sure if there are general fixes.

    March 18, 2008 at 8:44 pm
  • John

    Ascanio and others: Try using Stored Procedure, for this example.

    DROP PROCEDURE IF EXISTS test2.user_count $$
    CREATE DEFINER=root@localhost PROCEDURE user_count(param1 int)
    SELECT count(*) FROM comments WHERE user_id=param1;
    END $$

    Run the above code
    then CALL user_count(5); It will run SELECT count(*) FROM comments WHERE user_id=5; and just as fast as the straight select (from my testing less than 0.001 milisecond difference, sometimes the CALL was faster, but thats just due to random flux you might notice a difference if you were making thousands of calls a second..

    April 10, 2008 at 6:50 am
  • Bimal Poudel

    Views meant to hide the complexities in the user’s side. This helps you to shorten your query. Optimization is to be done from the MySQL server itself. But have you ever heard of something like indexing a view? Something else? As a developer, performance too matters.

    April 30, 2008 at 4:23 am
  • Dave

    This post makes it clear that indexes will not be used for views that use temporary tables. For views that do not use temporary tables, will ‘joining’ or ‘whereing’ on a column that is indexed in the underlying table use that index?

    July 12, 2008 at 6:15 pm
  • dendie

    nice articel… thanks for your backmarking… 🙂

    February 24, 2009 at 8:32 am
  • Blakkky

    LOL, very useless article!

    Author try to compare two uncompirable things!
    Look here: Simple select is slower then view-select (ROFL):
    Simple select:
    mysql> select count(*) from (select * from comments where user_id in (select user_id from comments where user_id = 5 group by user_id)) t2;
    | count(*) |
    | 100 |
    1 row in set (3.71 sec)

    And from VIEW (CREATE VIEW user_counts AS SELECT * FROM comments):
    mysql> select count(*) from user_counts where user_id = 5;
    | count(*) |
    | 100 |
    1 row in set (0.00 sec)

    Author’s problem NOT IN VIEW at all, but in using a view for non-view-based operations!

    VIEW – mechanism to make a pseudo-tables for database users (for example, to make four different client lists based on three tables (account, client, client-address) tables: client’s names list, client’s address list, client’s detail info list. On this lists client-side-software maps grids, combo-boxes and other.

    In author’s example, VIEW works slowly ONLY AND ONLY BECOUSE autor use a GROUP BY statement in VIEW!

    PS: sorry for my english, i’m not from english-speaking country 🙂

    August 24, 2009 at 9:19 am
  • Baron Schwartz

    Blakkky, you should learn the execution plan differences between joins and correlated subqueries.

    August 24, 2009 at 10:55 am
  • Jesse Price

    Hmm, it appears that using views which could be a quicker way to make db calls especially in joins and subqueries… never realized how bad the performance is by using them. I wonder if this performance issue also occurs when creating triggers and stored procedures.

    September 9, 2009 at 5:31 am
  • Blakkky

    Barton Schwartz, plz read my post MINDFULLY! My example is a joke, that shows two uncompairable things. Author’s post is same as mine, he compare a GROUP BY SELECT and SIMPLE SELECT and say, that VIEW, based on GROUP BY SELECT is slowly than SIMPLE SELECT. Is it a right comparasion?

    Again, author’s example is same as
    SELECT cnt FROM (SELECT user_id,count(*) cnt FROM comments GROUP BY user_id) AS user_counts WHERE user_id = 5;
    and he compare it with
    SELECT count(*) FROM comments WHERE user_id=5;

    In this case, first select (view) is SLOWLY, then simple select (it’s obvious, becouse “group by” with index filtering is slowly, that simple index filtering). Problem not in VIEW MECHANISM in mySQL at all, but in author’s approach to using view!

    Correct test is something like this:
    1st experiment:
    mysql> CREATE VIEW user_counts AS SELECT user_id, comment_id, message FROM comments;
    Query OK, 0 rows affected (0.02 sec)

    mysql> SELECT user_id, count(*) FROM user_counts WHERE user_id = 5;
    | user_id | count(*) |
    | 5 | 100 |
    1 row in set (0.00 sec)

    mysql> explain SELECT user_id, count(*) FROM user_counts WHERE user_id = 5;
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    | 1 | SIMPLE | comments | ref | PRIMARY | PRIMARY | 4 | const | 100 | Using index |
    1 row in set (0.00 sec)

    2nd experiment:
    mysql> SELECT user_id, count(*) FROM comments WHERE user_id = 5;
    | user_id | count(*) |
    | 5 | 100 |
    1 row in set (0.00 sec)

    mysql> explain SELECT user_id, count(*) FROM comments WHERE user_id = 5;
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    | 1 | SIMPLE | comments | ref | PRIMARY | PRIMARY | 4 | const | 100 | Using index |
    1 row in set (0.00 sec)

    In this case, this queries has EQUAL PERFORMANCE at EQUAL REQUESTS!

    PS: I know, that in my test there is no need to use VIEW, but it’s a TEST, not production solution!
    view is really need to make “interface pseudo-tables”, that uses from database-client software to make a database structure more flexible and make database-client applications not dependented from database real structure.

    December 21, 2009 at 7:11 am
  • Tiberius

    So if we have two or more large views that we would like to join are we pretty much stuck? Or is there such a thing as an indexed view?

    January 4, 2010 at 9:21 am
  • peter


    It is possible. Devil is as usually in details – MySQL have different ways of executions queries with views – if it just “merges” the query it may perform well if you get 2 large temporary tables which need to be joined on the final stage it will be rather slow.

    EXPLAIN should tell you that.

    January 4, 2010 at 9:39 am
  • Tiberius


    Thank you, I just did an “explain” on the query and it revealed some very interesting details!

    Could you perhaps rephrase this? I’m confused …

    “if it just “merges” the query it may perform well if you get 2 large temporary tables which need to be joined on the final stage it will be rather slow.”

    January 4, 2010 at 2:56 pm
  • Geoff

    I’m curious what steps / info we can refer Tiberius to for optimizing his query. I have a view in a query of my own and its speed is turtle-fast (sarcasm, hehe). The EXPLAIN convinces me that temporary tables are being built and then joined.

    However, since even when I copy my view data into a real table before doing the joins, adding the appropriate indexes, and still get horrible performance – it probably is more MySQL’s join that is failing me, rather than the view.

    January 4, 2010 at 3:01 pm
  • peter


    MySQL has 2 ways to deal with views – MERGE and TMPTABLE, see

    Looking at EXPLAIN you should see either all views collapsed and query looking as a simple join or you should see temporary table and probably 2 rows with “ALL” access type corresponding to doing full join on temporary table.

    Note if MySQL does “Merge” for view it does not matter it is able to execute join efficiently in all cases, but this is other topic.

    January 4, 2010 at 3:23 pm
  • Tiberius

    First of all, thank you both for working with me on this, and thanks for the heads up on the MERGE and TEMPTABLE algorithms. I’ll try creating the views with both algorithms and let you know how it goes.

    Basically, I’m in the process of importing the content of a pre-existing mysql db into Drupal, but the table and field names change when you do that. I’m looking to essentially create “aliases” from the new (Drupal) table and field names to the old ones, so that our HUGE perl library can continue to function without us rewriting every single query. So I thought views would be a good solution. I have yet to see if 5 or 6 views can be joined without a huge performance hit …

    January 4, 2010 at 3:37 pm
  • John Larsen

    Tiberius: You’re going to lose the ability to reliably write to the db from whatever application is using the views. MySQL views can be used for updates but its application is limited in many ways, you’d need to test all update scenarios as well.

    January 4, 2010 at 4:39 pm
  • Tiberius

    SOLVED! It turns out that I just needed to add indices. The column names in the ‘where’ clause of the slow query were not indices, so the query was running really slowly. Once I added the appropriate indices to the tables, THEN created the views everything worked! So it turns out that views were not the culprit at all, it was querying on “un-indexed” columns that was the problem.

    January 6, 2010 at 8:03 am
  • rajiv

    mysql view are good, but certain care is required –

    1. tables on which you want to create views, should have proper primary key and indexes (make sure you do it before dumping data in the tables)

    2. skip any where clause, or group by clause

    3. drop off any columns which you dont require, or create multiple views, based on the column required

    4. increase sql cache / memory (hosting providers … ahem ahem!)

    5. do not mix views with outer joins

    May 6, 2010 at 10:26 am
  • JackW

    Depending on how often you are updating the data, it can be a lot more efficient to use a stored procedure to generate a table – effectively a materialized view – rather than using an actual MySQL.

    In my application I have several hierarchies – customer, product, division. New products are added several times a day; new customers/divisions, much more rarely. If materialized views were supported, I would use that feature to de-normalize the static data so the frontend doesn’t need to generate a complex query to get the results it needs.

    Using a MySQL view was an option I considered, but quickly rejected on the basis that the server would generally re-evaluate the underlying query each time the view is queried.

    Because the write/query ratio on the static data is very low in my application, I have instead used stored procedures to drop and recreate the tables with the de-normalized data. This process takes a minute or two to perform (the normal tables are quite large), but given the rate of writes this is much more efficient than using even an optimized view.

    August 25, 2010 at 10:06 pm
  • roselan

    I just tried views, and indeed it was slow (overall for auto completion purposes).

    instead of “select * form my_view where my_field = $prm”, I create a temporary table at the start of the process “create temporary table my_temp as (select * from my_view)”, and do the selects on the temp table.

    Yet, mysql has a long walk to go in this domain compared to competition.

    November 22, 2010 at 9:49 am
  • RakamaGraham

    Roselan can you plz explain to me how you created the temp table as i dont know how to do it. Tried also to use view but that was really really slow so i cant use it. thanks for helping greetings Raka

    December 9, 2010 at 8:41 am
  • dhanesh mane

    very nice article, well as per my understanding Mysql views can be used for many reasons like to simplify complex queries and giving limited access data to the specific users. etc, I agree that there are few issues with it as long as performance is concerned but it also has great power.

    December 29, 2010 at 3:35 am
  • RakamaGraham

    i still got the same problem as mentioned before maybe someone can tell me how to solve it as i dont get it working. Would be nice if someone got mail me the answer or answer me in here. Thanks in advance

    January 12, 2011 at 3:37 am
  • Generika

    hmmmm i tried to use the sql code mentioned above but somehow i got an error in line 27 and i dont know where i did go wrong. i just copied it is the an error inside which i dont seE?

    January 14, 2011 at 1:59 am
  • Kurt

    The code did work out fine when I have tried it…Maybe you have used the wrong code!Anyways great blog. Keep up the good work.

    February 27, 2011 at 9:20 am
  • Jelly

    when i tried the code piece the last time it worked without any problems. now i wanted to use it on another project of mine but suddenly its seems not to work. but i have to say that i use a different server with other software now maybe there are some issues i hope i*’ll figure it out soon.

    March 3, 2011 at 12:57 am
  • Sakamagra

    i didnt encounter any problems when i was using the code what kind of server system do you use?

    March 3, 2011 at 4:59 am
  • shelley

    The complete syntax of the CREATE VIEW command looks like this:

    VIEW name [(columnlist)] AS select command

    ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED.

    By default, MySQL always uses UNDEFINED, if no ALGORITHM clause is present (can be determined via SHOW CREATE TABLE viewname).

    Mysql prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient.

    // ***Views have slow performance on tables with large amount of data

    April 19, 2011 at 5:01 am
  • Marry

    My experience is that troubles with MySql always come up when there are peaks of traffic. It’s for those peaks that you really have to optimize everything. Thanks for good advice, I’ll keep it in mind.

    July 21, 2011 at 6:02 am
  • Adam

    Great article – was really trying to decide if views were worth using as I’ve noticed the performance hit.
    My database isn’t so large and I’m the only one using it so I think I’ll just skip Views.
    Might write PHP functions so I can easily re-run complex queries.

    Would rather a fast complex query then a slow (but simpler to use) View.

    December 18, 2011 at 5:31 pm
  • Hans

    Is this still true for MySQL 5.5?

    December 8, 2012 at 2:35 am
  • Joel

    “Expanded as a macro or creating temporary tables”
    When would it benefit a view to create a temporary tables over expanding as a macro?
    And can one dictate the view to force it to run as a macro then to use the unindexed temporary table.

    Truly I thought (albit incorrectly) that views were equal or better than the select statement they were based off.
    But lo and behold to my surprise MySQL made it quite evident this is not that case.

    In my situation I had 5 tables left outer joining. (about 2 of those tables were in the thousands, one was in the tens of thousands). The view worked fine.

    Joined one more table and all hell broke loose! The select statement was almightily indexed for instant execution. The view however, did absolutely nothing for the longest amount of time after running a simple select off it.

    Dropped the view and recreated it. Recreated it as some other name. Restarted the db server. The view did nothing. Removed the newly joined table and created the view again. The view was meant to never run again. Sad really.

    Created a table off the select statement. Got a view to read from some simple wheres and voila!
    BUT, however, i miss the old view immensely and here I am hoping to get it back and running.

    Thanks! Good article m8!

    February 16, 2013 at 4:02 am
  • west

    i have been burned by ms access queries. now that i am into mysql… i certainly want to hear all these view stories. even though i have not yet used

    May 23, 2013 at 3:26 pm
  • west

    just to add. i am thinking of working with temporary values getting posted immediately after a transaction eg customer balance on the customers table.

    Then i wont need a view.

    May 23, 2013 at 3:29 pm
  • Andani Masikhwa

    I created a view for each and every teacher on a website with about 200 records each.

    It is a disaster, you can’t even login at times.

    Do you know how i can fix it?

    July 25, 2014 at 11:14 am
  • Sentil Muthiah

    Lot of Problems using view in my experience. One of the major issue is, if you add or remove index on the tables involved in the view, then now if you query on the view, you can see that view will not use the index properly. The only way to solve this, you need to drop the view and create it again.

    August 24, 2014 at 11:00 am
  • mevkok

    Showing rows 0 – 150 ( 1501 total, Query took 0.0228 sec) WITHOUT VIEW
    Showing rows 0 – 150 ( 1501 total, Query took 0.0025 sec) WITH VIEW

    THERE IS something wrong with your mysql i suppose ;] GET A BETTER SERVER PLS and stop CRYING ABOUT THE VIEWS !!!

    December 23, 2014 at 7:09 am
  • Massimiliano Alessandri

    I can confirm the same behaviour persists in MySQL 5.5.43 (bundled with Ubuntu Server 14.04).

    The following SELECT query on a view:

    SELECT distinct
    id_cruises, maps, steps_day, cities_name, id_cities, arrival_hour,
    departure_hour, cities_description, countries_name, countries_image_name,
    id_steps_types, steps_types_description, ” as boat_image_name
    FROM view_cruises_list_itinerary_front
    WHERE id_cruises = ‘1234567’
    and cities_lang = ‘FR’ and id_website = ‘2’
    group by steps_day, cities_name;

    took more than three seconds, while expanding the view:

    select distinct s.id_cruises AS id_cruises,cr.maps AS maps,s.steps_day AS steps_day,c.cities_name AS cities_name,c.cities_lang AS cities_lang,s.id_cities AS id_cities,s.arrival_hour AS arrival_hour,s.departure_hour AS departure_hour,c.cities_description AS cities_description,b.countries_name AS countries_name,d.image_name AS countries_image_name,s.id_steps_types AS id_steps_types,st.steps_types_description AS steps_types_description,c.id_website AS id_website,e.image_name AS boat_image_name,e.id_image_type AS id_image_type from (((((((steps s join steps_types_l10n st on((s.id_steps_types = join cruises cr on((s.id_cruises = join cities a on((s.id_cities = left join cities_l10n c on(( = c.id_cities))) left join countries_l10n b on(((a.id_countries = and (c.id_website = b.id_website) and (st.steps_types_lang = b.countries_lang)))) left join countries_images_l10n d on((( = d.id_countries) and (c.cities_lang = st.steps_types_lang)))) left join boats
    _images_l10n e on(((e.id_boats = cr.id_boats) and (e.boats_lang = st.steps_types_lang) and (e.id_image_type = 1))))
    WHERE id_cruises = ‘1234567’
    and cities_lang = ‘FR’ and c.id_website = ‘2’
    group by steps_day, cities_name;

    even though the resulting query may seem rather complex, execution took little more than one millisecond.

    Our view was expanded as a temporary table, and with many concurrent users this meant concurrently creating tens of Gb of temporary files. That’s because it was NOT just intended by MySQL as a macro, as we expected: instead, the generated resultset was written to disk then searched WITHOUT indexes. This could depend also from the presence of GROUP BY and ORDER BY clauses, but assumed such an inefficient implementation, we decided not to use MySQL views in our projects anymore in the future.

    June 30, 2015 at 11:52 am
  • Francisco

    Hi Peter, The difference in performance is eliminated over time since the view keeps a permanent table and just adds up rows as they match the query. Querying the table each time you need the information, will add up in processing time compare with the view already in existence. I really do not know how it worked in 2007 but at this point in time it works as explained.

    August 22, 2017 at 1:25 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.