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.