Why Does a View Query Exhibit a Performance Discrepancy Compared to a Direct Table Query?

MySQL View Query Performance Discrepancy

Problem:

A database table containing approximately 100,000 user records exhibits a significant performance difference between a direct query and one using a view. The query directly accessing the table has a plan cost of 5200, while the query accessing the view has a plan cost of 100,000.

Explanation:

The performance discrepancy is caused by the algorithm used to perform the view. The view in this case uses the "temptable" algorithm, which retrieves all rows from the underlying table into a temporary table before performing the filtering operation specified by the WHERE clause. In contrast, the direct query uses the "merge" algorithm, which performs the filtering operation directly on the indexed data of the underlying table.

The WHERE Clause in Views

The WHERE clause in a view is applied after the view retrieves all the rows from the underlying table. This means that even though the view may have been created with a specific filter criteria, the WHERE clause in a query against the view is still applied to each row retrieved from the temporary table.

Resolution

To fix this performance issue, the view should be created using the "merge" algorithm. This can be achieved by using the "materialized view" option. A materialized view is a pre-computed copy of a view. It is stored in a physical table, so that queries against the materialized view can use the "merge" algorithm for filtering.

Example:

To create a materialized view using the "merge" algorithm, use the following syntax:

<code class="sql">CREATE MATERIALIZED VIEW vw_users AS
SELECT state, COUNT(*) AS cnt
FROM users
GROUP BY state;</code>

Once the materialized view has been created, queries against it will have a lower plan cost and improved performance.

The above is the detailed content of Why Does a View Query Exhibit a Performance Discrepancy Compared to a Direct Table Query?. For more information, please follow other related articles on the PHP Chinese website!