Before & After: How MySQL Indexing Turned a 4-Second Query Into 40 Milliseconds

The Problem: A Dashboard That Made Everyone Wait

A few months ago I was handed a small but painful problem. A client's internal dashboard — built on PHP with a MySQL backend on shared cPanel hosting — was taking anywhere from 3.8 to 5 seconds to load the main reports page. Users were complaining. The client was apologetic. And honestly, the codebase wasn't doing anything crazy. It was just a handful of queries joining a few tables.

That's the thing about slow queries. They rarely look dangerous at first glance. It's only when you actually measure what's happening under the hood that the real culprit shows up.

This post is a real before-and-after breakdown of what I found, what I changed, and what the results looked like. No fluff — just the actual work.

The Setup

The database had three relevant tables:

• orders — ~180,000 rows
• order_items — ~540,000 rows
• customers — ~22,000 rows

The dashboard query was pulling a summarised report: total revenue per customer for the last 90 days, filtered by a status column, ordered by revenue descending, and limited to the top 50.

The Original Query (Before)

SELECT
  c.id,
  c.name,
  c.email,
  SUM(oi.price * oi.quantity) AS total_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY c.id, c.name, c.email
ORDER BY total_revenue DESC
LIMIT 50;

Nothing exotic. Just a join, a filter, a group, and a sort. But it was crawling.

Step 1: Actually Look at What MySQL Is Doing

The first thing I always do before touching anything is run EXPLAIN. Not guess. Not assume. Measure.

EXPLAIN SELECT
  c.id,
  c.name,
  c.email,
  SUM(oi.price * oi.quantity) AS total_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY c.id, c.name, c.email
ORDER BY total_revenue DESC
LIMIT 50;

The output told the story immediately. The orders table showed type: ALL — a full table scan across all 180,000 rows. The rows estimate was 178,432. MySQL was reading nearly the entire table just to find completed orders from the last 90 days.

The order_items table was also doing a full scan for every matched order row. And Extra showed Using temporary; Using filesort — MySQL was dumping results into a temp table and then sorting them on disk.

In short: full scan, no index, temp table sort. The trifecta of slow.

Step 2: Check What Indexes Already Existed

SHOW INDEX FROM orders;
SHOW INDEX FROM order_items;

The orders table only had a primary key on id. No index on status. No index on created_at. No index on customer_id.

The order_items table also only had its primary key. The foreign key column order_id — which was being used in the JOIN — had no index at all.

This was the root cause. Every join, every filter was forcing MySQL to read every single row.

Step 3: Add the Right Indexes

Here's where a lot of developers go wrong: they add single-column indexes on everything and hope for the best. But MySQL can only use one index per table per query in most cases, and a poorly chosen index can be almost as bad as none at all.

For the WHERE clause on orders, I had two conditions: status = 'completed' (low cardinality — only a few distinct values) and created_at >= ... (range query). The golden rule for composite indexes is: equality conditions first, range conditions last.

-- Composite index for the WHERE clause on orders
ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at);

-- Index on the foreign key used in the JOIN
ALTER TABLE orders
  ADD INDEX idx_customer_id (customer_id);

-- Index on order_items for the JOIN
ALTER TABLE order_items
  ADD INDEX idx_order_id (order_id);

Three indexes. That's it.

Step 4: Run EXPLAIN Again (After)

After adding the indexes, I ran EXPLAIN on the exact same query again. The difference was dramatic.

• type on orders changed from ALL to range
• rows estimate dropped from 178,432 to approximately 4,200
• key now showed idx_status_created being used
• order_items switched from ALL to ref using idx_order_id
• Extra no longer showed Using temporary; Using filesort for the heavy lifting

MySQL went from scanning 178k rows to scanning roughly 4k. The join on order_items was now using a direct index lookup instead of a full scan.

The Results

I timed the query before and after using PHP's microtime(true) wrapped around the PDO execute call. On the shared hosting environment (not a powerful server by any stretch):

• Before: 3.9 seconds average
• After: 38–45 milliseconds average

That's roughly a 100x improvement. The page load time dropped from nearly 4 seconds to under 200ms total (including PHP processing and rendering).

Indexes don't change what your query does. They change how MySQL finds the data to do it. That distinction is everything.

What I Learned (and What You Should Take Away)

1. Always start with EXPLAIN

Never optimise by feel. EXPLAIN gives you a map of what MySQL is actually doing. Look for type: ALL (full scan) and Using temporary; Using filesort in the Extra column — those are your red flags.

2. Foreign key columns almost always need an index

If you're joining tables on a column, that column should have an index. MySQL doesn't automatically add indexes to foreign key columns (unlike some other databases). Check every JOIN condition in your slow queries.

3. Composite index column order matters

Put equality conditions (=) before range conditions (>, <, BETWEEN, LIKE) in a composite index. MySQL can use the equality part to narrow down the dataset before applying the range filter. Flip the order and you lose most of the benefit.

4. Low-cardinality columns alone make poor indexes

A column like status with only 4 possible values isn't great as a standalone index — MySQL might decide a full table scan is cheaper. Pair it with a higher-cardinality column like a timestamp and it becomes powerful.

5. Indexes are not free

Every index you add slows down INSERT, UPDATE, and DELETE operations slightly because MySQL has to maintain the index. On a read-heavy reporting dashboard this tradeoff is obvious. On a write-heavy transactional table, be more selective.

A Quick Checklist for Your Own Slow Queries

1. Run EXPLAIN on the query and look for type: ALL
2. Check all JOIN columns — are they indexed on both sides?
3. Check all WHERE clause columns — are they indexed?
4. If using composite indexes, verify column order (equality before range)
5. Run EXPLAIN again after changes to confirm MySQL is using your new index
6. Time the query in your actual application, not just in phpMyAdmin

Final Thought

The codebase didn't need a rewrite. The server didn't need an upgrade. The query itself didn't even need to change. Three ALTER TABLE statements fixed a problem that had been frustrating users for months.

Query optimisation isn't magic — it's just knowing how MySQL reads your data and giving it a faster path to get there. Start with EXPLAIN, follow the evidence, and most of the time the answer is simpler than you expect.