Mastering MySQL View Optimization: Techniques, Limits, and Best Practices

Overview

The piece continues a series on MySQL view optimization, focusing on how versions prior to 5.6 handle view processing and rewrite.

Test Environment

Two tables are created together with a simple view, a complex view, and a view that uses UNION. A small data set is inserted to illustrate the behavior.

How MySQL ≤5.5 Handles Views

In MySQL 5.5 the function mysql_make_view() performs all view‑related work. During parsing, mysql_parse() expands view definitions that are eligible for merging, invoking open_and_lock_tables() → open_table() and open_new_frm() to read the view definition. After expansion, mysql_make_view() conducts syntax analysis and attempts a merge (view rewrite) when possible, pulling the view’s tables and WHERE conditions up to the outer query.

Conditions for View Merge

Only views satisfying both of the following can be merged:

The view is created without the VIEW_ALGORITHM_TMPTABLE flag, i.e., it does not rely on a temporary table.

The view definition lacks GROUP, HAVING, DISTINCT, LIMIT, or aggregate functions.

Thus MySQL 5.5 cannot rewrite complex views.

Derived Table Processing

MySQL 5.5 treats derived tables (subqueries in the FROM clause) as separate temporary tables. In a simple two‑table join example, MySQL materializes the derived table first, then joins it to the outer table, preventing a direct join between the original tables and causing poorer performance. This demonstrates the early optimizer’s weakness.

Derived Tables vs. SQL Standard

The select_type column shows DERIVED for subqueries in the FROM clause, indicating a derived table. The SQL standard does not restrict derived tables to the FROM clause, so MySQL’s handling deviates from the standard. An example query shows MySQL treating a subquery as a regular subquery rather than a true derived table.

Conclusion

The article ends with a note that further parts will continue the discussion of MySQL view optimization.