What is a MySQL covering index?

Question

I saw the documentation describing covering index:

covering index
An index that includes all the columns retrieved by a query.

Does it mean that the covering index is a specific index?

I think covering index is a phenomenon.

If i follow the description of the document, then please see the following sql statement:

create index idx_name_age on table（id, name）
select id, name from table where id = 1
select id, name, age from table where id = 1

idx_name_age is a covering index in first statement, the second one is not.

So I think instead: the covering index is a phenomenon rather than an index.

Answer 1

Let's say that "covering" is "an attribute of an INDEX relative to a particular SELECT.

Some examples:

select id, name from table where id = 1

    INDEX(id, name)       -- covering; best index
    INDEX(id, name, age)  -- covering, but overkill
    INDEX(age, name, id)  -- covering, but inefficient (might not be used)

select id, name, age from table where id = 1

    INDEX(id, name, age) -- Having `id` first is optimal, but any order is "covering"

As already pointed out, if this is InnoDB and the table has PRIMARY KEY(id), then none of these secondary indexes are worth having.

SELECT a FROM tbl GROUP BY b ORDER BY c

    No index is very useful since the GROUP BY and ORDER BY are not the same.
    INDEX(a,b,c)   -- in any order, is "covering"
    INDEX(b,c,a)   -- "covering", and perhaps optimal.
    INDEX(b,c,a,d) -- "covering", but 'bigger'

Bigger matters in small ways. When doing SELECT COUNT(*) FROM ..., InnoDB will (usually) pick the 'smallest' index to do the counting.

Another 'rule' is to avoid redundant indexes.

    INDEX(a,b)  -- Let's say you 'need' this one.
    INDEX(a)    -- Then this one is redundant and should be dropped.

Answer 2

is the covering index is a specific index?

Yes, it is an index purposefully designed to satisfy particular queries.

For this query

select id, name, age from table where id = 1

the covering index is a multicolumn index on (id, name, age) created by

create index idx_name_age on table（id, name, age）

How so?

id is the first column in the index because it's used in WHERE id = 1. It's first because MySQL indexes are generally BTREEs, randomly accessible in index order. So WHERE id = 1 can jump, in the index, to find those id values.

name and age also appear in the index because they appear in the SELECT clause. Because they are in the index, the query can be satisfied entirely from the index. This is good because it cuts down on reads from disk or ssd: MySQL doesn't have to use the index to find rows in the main table before it can satisfy the query.

An index on only (id, name) is not a covering index for the above query; the age column is missing.

This query

select id, name from table where id = 1

can also be satisfied from the (id, name, age) covering index. It also can be satisfied by an index on (id, name): that is a covering index for the second query (but not the first).

Your example illustrates of the glossary definition. Indexes gain query performance by using extra disk/ssd space to store data.

A Microsoft SQL Server user can declare an index like this:

create index idx_name_age on table (id) include (name, age)

In this index the values of name and age ride along with the id in the index, but the index isn't ordered by those included columns. So, updating the index takes less time.

If id is the table's primary key, none of this applies, either in MySQL's InnoDB or SQL Server. The table itself is an index on id; it's sometimes called a clustered index. But now we're getting into too much detail.

Covering indexes can provide phenomenal increases in query performance when used wisely. Read https://use-the-index-luke.com/ For indexes to help, you must design them to match your queries. That's a large part of the art of database optimization.