Covering Indexes in MySQL

A covering index is a normal index that provides requested data required for a query without having to access the table.

For instance you want to optimize a query country name for specific country_id in product_order table which has 5.000.000 data. Suppose you have an index only in country_id. Then the query and plan will be like below. The plan says it will use index lookup for access method. It means it will scan index ix_product_country then access actual table to retrive country.

(Query Plan with normal index)

explain   format=tree
select country
from product_order po
where country_id = 1

-> Limit: 200 row(s)   (cost=109432 rows=200)
    -> Index lookup on po using ix_product_country (country_id = 1)

explain   format=tree
select country
from product_order po
where country_id = 1

-> Limit: 200 row(s)   (cost=109432 rows=200)
    -> Index lookup on po using ix_product_country (country_id = 1)

explain   format=tree
select country
from product_order po
where country_id = 1

-> Limit: 200 row(s)   (cost=109432 rows=200)
    -> Index lookup on po using ix_product_country (country_id = 1)

Covering index scenario will look like below.

(Query Plan with covering index)

create index ix_country_id_name on product_order ( country_id, country ) ;

explain format=tree
select country
from product_order po
where country_id = 1 ;

-> Limit: 200 row(s) (cost=11391 rows=200)
    -> Covering index lookup on po using ix_country_id_name (country_id = 1)

create index ix_country_id_name on product_order ( country_id, country ) ;

explain format=tree
select country
from product_order po
where country_id = 1 ;

-> Limit: 200 row(s) (cost=11391 rows=200)
    -> Covering index lookup on po using ix_country_id_name (country_id = 1)

create index ix_country_id_name on product_order ( country_id, country ) ;

explain format=tree
select country
from product_order po
where country_id = 1 ;

-> Limit: 200 row(s) (cost=11391 rows=200)
    -> Covering index lookup on po using ix_country_id_name (country_id = 1)

Now the plan has changed. The plan says that it will use Covering index mechanism to return query.Which means it will not access actual table all data will be retrived within index. Thus original cost has been reduced to 11.391 from 109.432 which means on average 10x faster with covering index.

You can use covering index if number of select columns is low and want to optimize query even though it uses “index lookup” in original plan.