Why Does a View Show Significantly Higher Cost Than Executing Same Query on an Underlying Table?

MySQL View Performance Issues

Issue:

A user has a query against a table with 100,000 rows, which shows a high cost (5200) when filtering based on a specific state. Creating a view with the same aggregate query and filtering on the view results in a significantly higher cost (100,000).

Explanation:

The discrepancy in cost is due to the view algorithm used. The default algorithm for views is temptable, which retrieves all rows from the underlying table and then applies the filter. This can result in poor performance when the filtering is based on a small percentage of rows.

Resolution:

To fix this issue, the view should be defined with the merge algorithm, which allows indexes on the underlying table to be used and improves filtering efficiency. This can be done using the following syntax:

<code class="sql">CREATE OR REPLACE VIEW vw_users AS
SELECT state, COUNT(*) AS cnt
FROM users
WITH MERGE VIEW;</code>

With the merge algorithm, the query on the view can now leverage the index on the state column, reducing its cost and improving performance.

The above is the detailed content of Why Does a View Show Significantly Higher Cost Than Executing Same Query on an Underlying Table?. For more information, please follow other related articles on the PHP Chinese website!