It would be nice if the Temp Table algorithm would be renamed to Unscalable algorithm. Perhaps then it would provide more of a warning to developers when seeing this in a view definition - similarly when it says using temp table in the explain results. Just a tongue-in-cheek request for the most part but really it can be disastrous to the unaware.
The trouble is that if you do certain things in your view definition it'll switch over from the sane merge algorithm to the hopelessly inefficient temp table algorithm. This is no big deal if the data involved is small. But it's one of those things that will kill your performance as your data grows.
How best to deal with this though? It's been a problem since views were implemented over 5 years ago and I don't know of any effort to fix it. Does this sort of problem exist in other popular database systems?
Scroll down in the link below to where it discusses when the Merge Algorithm cannot be used to see what causes MySQL to degenerate into using the awful Temp Table algorithm: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/create-view.html
What's so bad about it? The temp table algorithm works like so:
- Run the view "AS IS" in the view definition without merging the query's where clause criteria into it.
- Dump the resulting data into a temp table.
- Filter the data in the temp table based on the query's where clause criteria - no indexes here either.
So you can imagine the hell involved here when you have a 50 million row table with a view definition like - silly example but you get the point:
create view last_order_date as
select max(order_date) last_date, username from orders group by username;
A user might then write:
select * from last_order_date where username = 'joejoe22'
2 hours later the result is returned...
So how best to deal with this situation?