What is Covering Index in MySQL: Explanation and Example

A covering index in MySQL is an index that contains all the columns needed to satisfy a query, so the database can get the results directly from the index without reading the full table. This makes queries faster because it avoids extra data lookups.

How It Works

Imagine you have a book with an index at the back that lists every topic and the page numbers where they appear. If the index also included a short summary of each topic, you wouldn't need to open the book to get the information. A covering index works similarly in MySQL.

Normally, when you run a query, MySQL uses an index to find rows but then reads the full table to get the rest of the data. With a covering index, all the columns your query needs are already in the index. So MySQL can answer the query by looking only at the index, skipping the slower table read.

This reduces disk access and speeds up query execution, especially for large tables or frequent queries.

Example

This example shows a table with an index that covers the query columns, allowing MySQL to use the index alone to answer the query.

sql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary INT
);

-- Create a covering index on (department, salary)
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Query that can use the covering index
EXPLAIN SELECT department, salary FROM employees WHERE department = 'Sales';

Output

id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: idx_dept_salary
key: idx_dept_salary
key_len: 102
ref: const
rows: 10
Extra: Using index

When to Use

Use a covering index when your queries select only a few columns and those columns can be included in a single index. This helps speed up read-heavy operations like reporting or searching.

For example, if you often query employee department and salary without needing other columns, a covering index on those two columns avoids reading the full table.

However, adding too many columns to an index can slow down writes, so balance is important.

Key Points

• A covering index contains all columns needed by a query.
• It lets MySQL answer queries using only the index, skipping the table.
• This improves query speed by reducing disk reads.
• Best for queries selecting few columns frequently.
• Too many indexed columns can slow down data changes.

Key Takeaways

A covering index lets MySQL answer queries using only the index without reading the table.

It improves query speed by reducing disk access and data lookups.

Create covering indexes on columns frequently queried together.

Avoid adding too many columns to indexes to keep write performance good.

Use EXPLAIN to check if your query uses a covering index (look for 'Using index').