MySQL Performance: I Cut My Query Time by 90% With Better Indexing

I'll never forget the moment my production database curled up and died.

It was a Tuesday. I'd just rolled out a "simple" feature — a dashboard showing all orders from the last 90 days, grouped by customer, with their total spend and most recent purchase date. Sounded innocent enough. The query looked fine in my local dev environment with its 200 test records.

Production had 847,000 orders.

The page took 23 seconds to load. My phone started buzzing. The CEO emailed me. I sat there staring at EXPLAIN output I didn't fully understand, watching a full table scan on a table with half a million rows.

That was the day I decided to actually learn how MySQL indexes work. Not just "add an index on the column you're filtering by" — which is what every tutorial tells you and is honestly wrong half the time.

Here's what I wish someone had explained to me years earlier.

What an Index Actually Does (Skip This If You Know)

An index is a sorted copy of a subset of your table's data. MySQL uses it to find rows without scanning every row. Think of it like the index at the back of a textbook — instead of reading every page to find where "database indexing" is discussed, you check the index, get "p. 142-148", and flip straight there.

Without an index, MySQL reads every row (a "full table scan"). With an index, it does a B-tree search — which for a table with a million rows means about 20 lookups instead of a million.

That's the theory. Here's where it gets interesting.

The Single Biggest Mistake I Made

I used to index individual columns. Every column I might filter on got its own index. That's what the tutorials said, right?

-- Bad: separate indexes
CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_created_at ON orders (created_at);

Then I'd write a query like this:

SELECT COUNT(*), SUM(total)
FROM orders
WHERE status = 'completed'
  AND created_at > NOW() - INTERVAL 30 DAY
  AND customer_id = 8472;

And MySQL would pick one index — usually the most selective one — and then filter the rest row by row. My three separate indexes were barely better than no indexes at all for this query.

What I Should Have Done: Composite Indexes

A composite index (or multi-column index) covers multiple columns in a single B-tree. The order of columns in the index declaration matters enormously.

-- Good: composite index
CREATE INDEX idx_customer_status_date ON orders (customer_id, status, created_at);

This single index can satisfy the entire WHERE clause. MySQL walks the B-tree once — narrow by customer_id, then by status, then by created_at — and it already has the exact rows it needs.

But there's a catch.

The Leftmost Prefix Rule

Composite indexes only work if your query conditions use the columns from left to right. If you have INDEX (a, b, c), these queries use the index:

WHERE a = 1           -- ✓ uses index
WHERE a = 1 AND b = 2 -- ✓ uses index
WHERE a = 1 AND c = 3 -- ✓ uses index for 'a', but not 'c'
WHERE b = 2           -- ✗ full table scan

This means you need to think carefully about which columns to put first. The general rule:

• Equality columns first — put WHERE x = ? columns before range columns
• Most selective first — the column that eliminates the most rows should be leftmost
• Range columns last — once MySQL hits a range condition (>, <, BETWEEN, LIKE without leading wildcard), it stops using further columns in the index for filtering

How I Actually Fixed That Dashboard Query

Here's the real query that had been killing my dashboard:

SELECT
  c.name,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent,
  MAX(o.created_at) AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed'
  AND o.created_at >= '2026-04-16'
GROUP BY c.id
ORDER BY total_spent DESC
LIMIT 50;

After understanding composite indexes, I created:

CREATE INDEX idx_orders_completed_date
  ON orders (status, created_at, customer_id);

Query time: 23 seconds → 0.4 seconds.

Was that the end? No. I also needed a covering index.

Covering Indexes: The Secret Weapon

A covering index contains all the columns a query needs, so MySQL never has to touch the actual table — it reads everything from the index itself. This is significantly faster because indexes are more compact and cached more aggressively.

-- Covering index for the join + aggregation
CREATE INDEX idx_orders_covering
  ON orders (status, created_at, customer_id, total, id);

Now MySQL reads everything from the index. No table lookups at all. The query dropped to 0.08 seconds.

I keep this exact pattern stored in Snippet Ark so I never forget it. Honestly, having a well-organized snippet library for query patterns like this saves me more time than any AI assistant when I'm in the zone.

When Indexes Hurt

Indexes aren't free. Every index you add:

• Slows down writes — MySQL has to update every index on INSERT, UPDATE, and DELETE
• Takes disk space — indexes can be larger than the table itself
• Can confuse the optimizer — too many options and MySQL may pick the wrong one

My rule of thumb: no more than 5-6 indexes per table for a typical OLTP workload. If you're adding more, you're probably missing a composite index that could replace three singles.

Tools like pt-index-usage from Percona Toolkit can analyze your slow query log and tell you which indexes are never used. I run this quarterly and delete the dead weight.

Most Useful Things I Check First When a Query Is Slow

1. Run EXPLAIN

Just put EXPLAIN before your SELECT. Look for type: ALL (full table scan), rows (how many rows MySQL examined), and Extra: Using filesort or Using temporary (both bad).

EXPLAIN SELECT ...

2. Check for implicit type conversion

This one bit me hard:

-- If customer_id is INT but you pass a string
SELECT * FROM orders WHERE customer_id = '8472';

MySQL casts every row's customer_id to a string before comparing. Index ignored. Always match your column types.

3. Avoid SELECT * in queries with joins

Grabbing every column forces MySQL to read the table even when a covering index would suffice. Be explicit about what you need.

4. Watch out for ORDER BY + LIMIT

If MySQL can't satisfy the ORDER BY from an index, it reads all matching rows, sorts them, then trims to LIMIT. That's a lot of wasted work. Add an index that matches the sort order.

-- This benefits from INDEX (status, created_at)
SELECT * FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 20;

The Mental Model That Finally Made It Click

Someone explained indexes to me like a phone book (dating myself a bit here, but bear with me).

If you need to find "John Smith" in a phone book:

• No index — Read every name on every page. Good luck.
• Index on last name — Go to the "S" section, find Smith, read all Smith entries. Fast.
• Composite index on (last_name, first_name) — Go directly to Smith, John. One entry. Done.

Same thing. The phone book is a clustered index ordered by last name, then first name. If you want to find everyone named "John" regardless of last name, that phone book is useless — you're back to scanning every page.

That's why column order in composite indexes matters so much.

Tools That Help

Beyond EXPLAIN and pt-index-usage, I rely on a few things:

• MySQL Workbench — visual EXPLAIN is way easier to read than raw output
• phpMyAdmin — quick EXPLAIN and index management without SSH
• My own snippet collection — I keep my most-used indexing patterns, EXPLAIN cheat sheets, and migration templates saved locally. Snippet Ark is perfect for this because it's local-first and doesn't phone home with my production schema

What I'd Tell My Younger Self

Don't add indexes blindly. Start with the queries your app actually runs — check your slow query log, pick the worst offenders, and build composite indexes that cover them completely. Delete indexes nothing uses. Repeat once a quarter.

That's it. That's 90% of the performance gain with 10% of the effort.

What's the slowest query you've ever had to fix? I'm genuinely curious — drop it in the comments or save the pattern to your own snippet library. I've got mine waiting in Snippet Ark if you need a place to start organizing yours.