Covering index and MySQL

12 Mar 2017 in MySQL

In most cases, an index is used to quickly locate the data records from which the required data is read. Additional roundtrips to the database tables required to fetch the data.

A covering index is a type of index where the index itself contains all required data fields or, in other words, all fields selected in a query are covered by an index. This eliminates the additional roundtrips to the database tables, which is I/O bounded, thus improving performance. Note that in MySQL, this applies only to InnoDB tables.

Also beware that using many fields on an index will degrade the performance of some queries like INSERT, UPDATE and DELETE.