What is a covering index, and how can it reduce disk I/O?

Question

Accepted Answer

A covering index is an index that contains all the columns required to satisfy a query—both for filtering and for returning results. When MySQL can read all needed data directly from the index without accessing the table, this is called an *index-only scan*. The index includes all columns in the SELECT list. The index includes all columns in the WHERE clause. The index includes all columns in ORDER BY or GROUP BY (optional but beneficial). MySQL does **not** need to read row data from the table (clustered index). All required data is fetched directly from the smaller, faster index structure. This reduces random I/O operations, improving query performance. Useful especially for large tables where table-row access is expensive. SELECT name, age FROM users WHERE email = 'a@test.com';
-- Index exists only on (email) MySQL will use the email index only to find matching rows, then perform extra lookups on the table to fetch `name` and `age`. CREATE INDEX idx_user_cover ON users(email, name, age);

SELECT name, age FROM users WHERE email = 'a@test.com'; Now the index includes all requested columns, so MySQL can resolve the query entirely from the index—no table access needed. Reduced disk reads because table rows are not accessed. Faster query execution via index-only scans. Smaller index pages lead to fewer cache misses. Improves performance for read-heavy workloads. For frequently executed SELECT queries. When reading only a few columns repeatedly. For reporting or analytics workloads. In high-read OLTP systems.

What is a covering index, and how can it reduce disk I/O?

Understanding Covering Indexes and How They Reduce Disk I/O

What is a covering index, and how can it reduce disk I/O?

Understanding Covering Indexes and How They Reduce Disk I/O

Scenario Questions

Follow-up Questions