Optimize MySQL Indexes with Composite and Covering Indexes

You can optimize MySQL indexes with a composite covering index by matching the index column order to the query's WHERE, JOIN and ORDER BY clauses, then confirming the plan with EXPLAIN. This guide designs one index for a real query, adds it without blocking reads where MySQL supports it, and shows how to read the output so you know the index is actually used.

Before you start

You need:

• A Linux VPS running MySQL 8.0/8.4 or MariaDB on Ubuntu 22.04/24.04, Debian 12, AlmaLinux or Rocky Linux.
• A MySQL user with the INDEX privilege on the target table, or root access.
• A staging copy of the table for any large schema change.
• The slow query text. If you do not have it yet, start with Find slow queries in MySQL with the slow query log.

Connect to MySQL and select the application database:

mysql -u root -p

USE shop;
SELECT VERSION();

Read the query before you index it

Index design starts with the query, not with the table. For this example, assume an InnoDB table:

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_customer (customer_id)
) ENGINE=InnoDB;

The slow query is a customer order list:

SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Check the current plan:

EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20\G

You will likely see key: idx_customer and Extra: Using where; Using filesort. The single-column index finds the customer's rows, but MySQL still has to filter by status and sort by created_at.

Build a MySQL composite covering index

A composite index has more than one column. MySQL uses the leftmost columns first, so order matters:

1. Put equality columns first. Columns compared with = or IN work best at the front.
2. Put the range or sort column next. A column used in ORDER BY belongs after the equality columns.
3. Keep the index as narrow as practical. Every indexed column is stored in the secondary index.

For the query above, customer_id and status are equality columns, and created_at is the sort column. Create the index in that order:

ALTER TABLE orders
  ADD INDEX idx_orders_customer_status_created (customer_id, status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE, LOCK=NONE lets reads and writes continue on MySQL versions that support it. If your version rejects those options, run the ALTER TABLE without them and use a maintenance window.

MySQL 8.0 can scan an ascending index backward for ORDER BY created_at DESC, so a descending index is not automatically required. Use a descending index only when the query and the EXPLAIN output show it is needed.

Add covering columns to the index

A covering index contains every column the query needs, so MySQL answers the query from the index without reading the table rows. InnoDB secondary indexes already include the primary key column, id, so you only need to add total:

ALTER TABLE orders
  ADD INDEX idx_orders_cover (customer_id, status, created_at, total),
  ALGORITHM=INPLACE, LOCK=NONE;

The query selects id, created_at and total. The new index provides all three: id is implicit, created_at is in the index, and total is now included. The query no longer needs a table lookup for the selected columns.

If the application also runs SELECT * FROM orders for the same filter, the covering index will not cover that query. Keep SELECT * out of high-traffic paths and list only the columns you need.

Check it works

Run EXPLAIN ANALYZE on MySQL 8.0.18 and later. It executes the query and shows the actual plan and timing:

EXPLAIN ANALYZE
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20\G

Look for these signs:

• key: idx_orders_cover — the new composite index is chosen.
• type: ref — MySQL uses an equality lookup rather than a full scan.
• Extra: Using index — the query is covered by the index.
• No Using filesort and no Using temporary for this query shape.

Also confirm the index exists with the expected column order:

SHOW INDEX FROM orders;

The Seq_in_index values should read 1 for customer_id, 2 for status, 3 for created_at and 4 for total.

Test with a real customer ID that has many orders. If the optimizer still chooses the old index, run ANALYZE TABLE orders; and check the plan again.

Troubleshooting

• EXPLAIN still shows Using filesort — the sort column is in the wrong position, or an earlier column uses a range. Rebuild the index with equality columns first and the ORDER BY column after them.
• ALTER TABLE fails with ALGORITHM=INPLACE is not supported — the storage engine or MySQL version cannot build that index online. Remove ALGORITHM=INPLACE, LOCK=NONE and run the change in a maintenance window.
• The index is not used after a data type change — MySQL may skip the index when the column and the compared value have different types. Check SHOW CREATE TABLE orders; and compare the application's parameter types.
• Using index does not appear — the query selects a column that is not in the index. Add the column to the index only if the read benefit is worth the extra write cost.
• Duplicate or redundant indexes appear in SHOW INDEX — remove the old index only after confirming the new one handles the workload. Keep a backup of the table definition before dropping anything.

Next steps

• Find the queries worth indexing with Find High-Cost MySQL Queries with Performance Schema.
• Reduce disk reads for indexed lookups with Tune MySQL InnoDB Buffer Pool Size for a VPS.
• Review overall server memory before adding more indexes with Tune MySQL and MariaDB memory settings for a small VPS.