How Can I Get MySQL to Use Indices for View Queries?

Getting MySQL to Utilize an Index for View Queries

Query: How do I encourage MySQL to leverage an index for a view query?

Answer: While using views in MySQL may not be ideal, MySQL can utilize indices for view queries when provided with appropriate index structures.

Detailed Explanation:

MySQL's view queries are distinct from other databases because the optimizer does not "push" predicates from the outer query into the view query. In this case, the outer query filters on happened_in, but MySQL does not apply this filter when executing the view query.

As a result, the view query is processed separately, creating a temporary table (a derived table). When the outer query runs, it uses the derived table as a row source, evaluating the happened_in = 2006 predicate then.

To improve performance, a covering index can be defined:

CREATE INDEX highscores_IX3 ON highscores (player, happened_in, score)

This index includes all columns referenced in the view query, with player as the leading column (equality predicate) followed by happened_in (GROUP BY). Including score makes the index a covering index, allowing MySQL to retrieve all necessary data from the index alone.

Alternatively, consider using a standalone query that does not utilize a derived table, which can provide better performance in some cases:

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

With this modification and an appropriate covering index, MySQL should be able to optimize the view query, significantly improving performance by leveraging the available indices.

The above is the detailed content of How Can I Get MySQL to Use Indices for View Queries?. For more information, please follow other related articles on the PHP Chinese website!