How to Choose the Right Indexes for MySQL Query Optimization

The article focuses on practical techniques for building optimal indexes in MySQL‑family databases (MySQL, MariaDB, PerconaDB) without delving into every internal algorithm detail.

Rule‑based steps to construct a compound index

List every table used in the query and, for each sub‑query, create an independent list of tables. For a query with two SELECT sub‑queries you end up with three lists, one for the outer query and one for each sub‑query.

In each index, place the columns that are compared with equality operators (e.g., age = 25) as the left‑most columns.

Select a single "range column"—the column used with range operators ( <>, >, <, IN(), BETWEEN, LIKE). Put this column after the equality columns; it should filter the most rows.

If the query has no range column, consider adding columns from the GROUP BY clause.

If there is still no range column and no GROUP BY, add columns from the ORDER BY clause.

Sometimes a separate index that exactly matches the ORDER BY columns can help, but it must contain the columns in the same order (ASC/DESC) as the query.

Finally, add any columns needed for a covering index (i.e., columns appearing in the SELECT list) so the query can be satisfied entirely from the index.

Concrete example

SELECT id, first_name, last_name, age FROM employees WHERE first_name = 'John' AND last_name = 'Brack' AND age > 25 ORDER BY age ASC;

Following the rules, the recommended index for the employees table is: employees (first_name, last_name, age, id) This index starts with the equality columns ( first_name, last_name), then the range column ( age), and finally the id column to make the index covering.

What not to do when indexing or writing SQL

Creating a separate index for every column

MySQL can usually use only one index per table in a query. Individual indexes on each column prevent the optimizer from using multiple indexes simultaneously, leading to slower execution. Use composite indexes instead.

Using OR in filter conditions

Queries that contain OR between columns often cannot use an index, making them unindexable. Split the query into separate SELECT statements combined with UNION ALL (or UNION DISTINCT if duplicates must be removed).

Incorrect column order in an index

Index column order matters. An index that begins with the column used in the equality filter is useful, while a reversed order may be useless. For example, (first_name, last_name) is optimal for WHERE first_name = 'John', whereas (last_name, first_name) cannot be used to filter on first_name first.

Redundant indexes and maintenance overhead

Each index must be kept up‑to‑date on INSERT, UPDATE, and DELETE operations, which adds write overhead, especially for large tables. Create indexes only when they provide a clear performance benefit, and periodically analyze the schema to drop redundant or unused indexes.