Re:Disaster: MySQL 5.5 Flushing
by mrinterweb on Sunday September 18, 2011 @08:31PM
(#3011719)
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.
[ Parent
| on HN ]
That sounds like a bad data model and/or bad MySQL usage to me.
Re:Disaster: MySQL 5.5 Flushing
by mrinterweb on Monday September 19, 2011 @02:09AM
(#3012512)
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.
Re:Disaster: MySQL 5.5 Flushing
by IgorPartola on Monday September 19, 2011 @07:38AM
(#3013127)
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.
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.
Re:Disaster: MySQL 5.5 Flushing
by IgorPartola on Monday September 19, 2011 @11:42AM
(#3013952)
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.