MySQL MERGE and TEMPTABLE Algorithms for Views

I’ve learned a bit more about views in MySQL, and it’s worth mentioning that in some cases they can be far less efficient than their underlying query.

One thing to try to do with any view you build is add the following to the CREATE portion:

CREATE ALGORITHM = MERGE VIEW v_whatever

If you get a warning that MySQL can’t use the MERGE algorithm, be sure to test your view for performance. In this case, it uses the TEMPTABLE algorithm.

Some restrictions on using MERGE are when you have an aggregate in the SELECT, and also when using ORDER BY.

Author: Alex T. Silverstein

A developer who wants to help you and himself by writing down good tidbits for free

Leave a Reply

Your email address will not be published. Required fields are marked *