Can MySQL Views Leverage Indexes for Efficient Queries?

Can MySQL Views Leverage Indexes for Queries?

It is not straightforward for MySQL to utilize indexes for queries on views. Despite having indexes on underlying tables, view queries are processed separately, potentially leading to inefficiencies.

Solution: Covering Index

The optimal solution is to create a "covering index" on the underlying table. This index should include columns referenced in the view's defining query and any columns used for grouping. A covering index allows MySQL to satisfy the view query directly from the index, bypassing the need to access the underlying table pages.

For instance, considering the provided example with the highscores table, a covering index can be:

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

With this index in place, MySQL can leverage it for the view query:

SELECT * FROM v_kobe_highscores WHERE happened_in = 2006;

Why Views in MySQL Can Be Problematic

Unlike some other databases, MySQL does not "push" predicates from outer queries into view queries. Instead, the view query is executed separately, materializing an intermediate table that contains all rows from the view definition. The outer query is then applied to this derived table.

This process can lead to performance degradation, especially for views defined across large tables.

Alternative: Indexed Standalone Query

In some cases, it may be more efficient to execute a standalone query instead of using a view. For example:

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

With a suitable covering index on the highscores table, this query can retrieve the desired data efficiently without the overhead of creating and materializing a view.

The above is the detailed content of Can MySQL Views Leverage Indexes for Efficient Queries?. For more information, please follow other related articles on the PHP Chinese website!