4 ms·

I have personally noticed MySQL having horrible performance for views that use joins. Every time a view does a join, it creates a temporary table and you might

I have personally noticed MySQL having horrible performance for views that use joins. Every time a view does a join, it creates a temporary table and you might as well throw indexing out once that happens. I had a query that was taking 4 seconds with a view and used the same sql minus the view and got it down to 5ms. I know I am not being specific to flushing, but still it is an example of a potential performance problem.

rhizome 15y ago
That sounds like a bad data model and/or bad MySQL usage to me.
- mrinterweb 15y ago
  I recently did learn about the difference between the merge and temptable algorithm approaches a MySQL view can take. I was using the default temptable approach and did not know about the merge algorithm that can be specified for views. By my experience with MySQL's views, MySQL does not do much to optimize execution plans for views.
  - IgorPartola 15y ago
    By default MySQL will use the merge algorithm. It will create a temp table if the result of the view is large. You can control what "large" means. That is the better method of controlling the behavior, than potentially telling MySQL that it jeeds to create a multi GB result set but must keep it all in RAM. Read the manual or the O'Reilly book for more info. Anither option is to use materialized views, which are not natively supported in MySQL, but can easily be simulated.
    - jeltz 15y ago
      I do not see why views need to be so complex in MySQL. Why not do like in PostgreSQL where they are almost just subqueries saved within the database.
      - IgorPartola 15y ago
        That is essentially what they are. The merge algorithm merges your query with the query of the view and returns the result. However, if the query of the view returns a very large result set, it is much faster to pre-flight it, then select against it.
        jeltz 15y ago
        Yeah, but letting users specify if views are MERGE or TEMPTABLE seems quite pointless. Why not always use UNDEFINED and let the planner chose. The planner knows which tables are large.

rhizome 15y ago

That sounds like a bad data model and/or bad MySQL usage to me.

mrinterweb 15y ago
I recently did learn about the difference between the merge and temptable algorithm approaches a MySQL view can take. I was using the default temptable approach and did not know about the merge algorithm that can be specified for views. By my experience with MySQL's views, MySQL does not do much to optimize execution plans for views.
- IgorPartola 15y ago
  By default MySQL will use the merge algorithm. It will create a temp table if the result of the view is large. You can control what "large" means. That is the better method of controlling the behavior, than potentially telling MySQL that it jeeds to create a multi GB result set but must keep it all in RAM. Read the manual or the O'Reilly book for more info. Anither option is to use materialized views, which are not natively supported in MySQL, but can easily be simulated.
  - jeltz 15y ago
    I do not see why views need to be so complex in MySQL. Why not do like in PostgreSQL where they are almost just subqueries saved within the database.
    - IgorPartola 15y ago
      That is essentially what they are. The merge algorithm merges your query with the query of the view and returns the result. However, if the query of the view returns a very large result set, it is much faster to pre-flight it, then select against it.
      - jeltz 15y ago
        Yeah, but letting users specify if views are MERGE or TEMPTABLE seems quite pointless. Why not always use UNDEFINED and let the planner chose. The planner knows which tables are large.