Why Can't MySQL Use Indexes for View Queries?

Can't MySQL Use Indexes for View Queries?

Despite facing doubts about the effectiveness of views, the problem you face arises from the optimizer's inability to push predicates into the view query.

MySQL executes view queries separately, materializing the results in an intermediate "derived" (MyISAM) table before executing the outer query. This process skips the consideration of predicates from the outer query during the view query execution.

Addressing the Performance Issue

To improve the performance of your view query, optimize the index structure:

Create a Covering Index:

Define a "covering" index that includes all columns referenced in the view query. In this case, create the following index:

ON highscores (player, happened_in, score)

This index will allow MySQL to access the view query with "Using index" and avoid the more expensive "Using filesort."

Evaluate a Standalone Query:

Compare the execution plan of your view query with a standalone query that serves a similar purpose:

SELECT player
     , MAX(score) AS highest_score
     , happened_in
 FROM highscores
WHERE player = 24
  AND happened_in = 2006
GROUP
   BY player
    , happened_in

This standalone query can also utilize a covering index, but it eliminates the overhead of materializing an intermediate table.

Conclusion:

The key to optimizing view queries in MySQL lies in designing suitable indexes that the optimizer can utilize. View queries are essentially separate queries that are materialized before being referenced by outer queries. Understanding this behavior is crucial for avoiding performance pitfalls that may arise when using views in MySQL.

The above is the detailed content of Why Can't MySQL Use Indexes for View Queries?. For more information, please follow other related articles on the PHP Chinese website!