MySQL Indexes: Speed Up Slow Queries

The orders page was fast when it had ten thousand rows. Now it has two million rows and opening it takes several seconds each time. The code hasn't changed and the server is the same; the only thing that changed is the amount of data. In most of these cases, the problem is a missing suitable index.

An index can be compared to the index at the back of a book. Without it, you have to flip through every page to find a topic. With it, you go straight to the right page. This article shows how to find the slow query, read what happens with EXPLAIN and create the right index. If you haven't finalized your table design yet, also see designing database tables.

First, find the slow query#

Guessing isn't the right approach. First make sure which queries are slow. MySQL can log slow queries, which you can turn on (on shared hosting you may not have access and will have to ask support or use control panel tools):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

In WordPress there are also plugins that show the queries of each page. Either way, the goal is to isolate one specific query and work on it.

Reading EXPLAIN#

Consider the orders table:

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

The query below fetches a user's latest orders:

EXPLAIN SELECT id, total, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

In the output, look at these columns:

If type is ALL, key is empty and Extra contains Using filesort, you have seen exactly what makes the query slow. In newer MySQL versions you can also try EXPLAIN ANALYZE, which shows the actual execution time.

Creating an index#

For this query, a composite index on user_id and created_at is suitable:

ALTER TABLE orders
  ADD INDEX idx_orders_user_created (user_id, created_at);

Now run EXPLAIN again. key should be this index and rows should drop. Because the index covers both the user_id filter and the created_at order, no separate sort is needed either.

On a large, busy table, creating an index can take a while and put load on the table. Before running it on the main server, take a backup, and ideally do it in a low-traffic hour. Automatic database backup is a useful tool here.

Column order in a composite index matters#

An index on (user_id, created_at) is like a phone book sorted first by last name and then by first name. You can search by last name, or by both; but not by first name alone. A practical rule:

1. Put columns compared with = first.
2. Then the column used for a range (>, <, BETWEEN) or for ordering (ORDER BY).
3. An index (a, b) also works in place of a separate index (a), so building both is usually unnecessary.

Things that make an index useless#

Even with the right index, some ways of writing a query make it pointless:

-- Bad: function on the column, the created_at index isn't used
SELECT id FROM orders WHERE DATE(created_at) = '2026-10-01';

-- Good: a range on the column itself
SELECT id FROM orders
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02';

-- Bad: LIKE with a percent sign at the start
SELECT id FROM users WHERE email LIKE '%@example.com';

-- Better: LIKE that starts from the beginning of the value
SELECT id FROM users WHERE email LIKE 'ali%';

Another problem is comparing mismatched types; for example, comparing a string column with a number. MySQL may have to convert each row's value and the index becomes useless. For searching text in non-English languages, also look at searching Persian text in MySQL, since its problems are of a different kind.

Covering indexes#

If all the columns the query needs are inside the index itself, MySQL doesn't touch the main table at all. This is called a covering index, and in EXPLAIN it shows as Using index in the Extra column. For the query above, you can add total to the index too:

ALTER TABLE orders
  ADD INDEX idx_orders_user_created_total (user_id, created_at, total);

This makes reads faster but makes the index bigger. It is only worth it for very frequent queries.

The cost of an index#

An index isn't free. Every INSERT, UPDATE and DELETE has to update the indexes too, and every index needs disk space. So:

• Find and remove duplicate and unused indexes.
• Columns with very low variety (like gender or a binary status) are not good indexes on their own.
• After each new index, confirm with EXPLAIN that it is actually used.

To drop an index that has no benefit:

ALTER TABLE orders DROP INDEX idx_orders_user_created;

Summary#

• First identify the slow query, then create an index; guessing isn't enough.
• Read EXPLAIN: type=ALL, key=NULL and Using filesort are the warning signs.
• Start a composite index with equality columns and end it with the range or order column.
• A function on a column and LIKE '%...' make the index useless.
• Every index has a write cost and a space cost; create them only for real queries.
• Take a backup before changing a large table.