3 ms·
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
by 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 agoBy 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 agoI 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 agoThat 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 agoYeah, 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.