MySQL 8 Administrator???s Guide

Indexes

A table's primary index must be as short as feasible. This enables easy identification of each row. It is efficient, too. In the case of InnoDB tables, the primary key column is duplicated in each secondary index entry. If we have a short primary key, it saves space in the case of many secondary indexes.

We should create only those indexes which improve query performance. The indexes improve information retrieval, but they slow down the insert and update operations. Indexes must be created with proper attention to the performance impact. If it is required to access a table by searching on a combination of columns, it is preferred to have a composite index on the combination of columns, rather than a separate index on each of the columns. ...