I had a last work session last night, troubleshooting one of the project’s database performance issues. Without giving more details (at least for now), I want to save the link to MySQL view processing algorithms for future me.
For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.
A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.
A particular heavy query, using views, kept going into “Copying to tmp table” state, locking up the server and slowing everything to a crawl. Upon closer examination, the view was created without specifying the algorithm (UNDEFINED). Changing the view to use TEMPTABLE made everything so much faster.
I knew there were reasons for me being against using views in MySQL, but I could never remember them. This is one. Views not supporting indexes is another.
