A covering index is the fastest way to select data from a table.
Let's see how it works using a query that selects employees with a certain salary:
select id, name from employees where salary = 90;
No index vs. Using an index
If there is no index, the database engine goes through the entire table (this is called a "full scan"):
QUERY PLAN `--SCAN employees
Let's create an index by salary:
create index employees_idx on employees (salary);
Now the database engine finds records by salary in the index (this is faster than going through the entire table). And for each record found, it accesses the table to get the
QUERY PLAN `--SEARCH employees USING INDEX employees_idx (salary=?)
Using a covering index
Let's create a covering index (which covers all selected columns):
create index employees_idx on employees (salary, id, name);
Now the database engine works only with the index, without accessing the table at all. This is even faster:
QUERY PLAN `--SEARCH employees USING COVERING INDEX employees_idx (salary=?)
However, simply covering all columns used in a query may not be enough. The order of the columns should allow for a fast search using the index.
Suppose we build an index with the same set of columns, but in a different order:
create index employees_idx on employees (id, name, salary);
Now the database engine won't be able to quickly find records with
90. It may still use the index, but it will be a full index scan instead of a search (which is slow).
QUERY PLAN `--SCAN employees USING COVERING INDEX employees_idx
(note SCAN instead of SEARCH here)
Covering indexes cost more when the data in the table changes, so don't create them for every type of query. Often this is one of the last optimizations after everything else has been done.
Subscribe to keep up with new posts.