Optimizing a MySQL query on 300k+ records

The problem

An orders table with ~300k records. The following query takes 12 seconds:

SELECT o.id, o.created_at, u.name, u.email,
       COUNT(oi.id) as item_count,
       SUM(oi.price * oi.quantity) as total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
  AND o.created_at >= '2026-01-01'
  AND o.created_at < '2026-04-01'
GROUP BY o.id, u.name, u.email
ORDER BY o.created_at DESC
LIMIT 50;

Analyze with EXPLAIN

EXPLAIN SELECT ...;

+----+-------+--------+------+------+---------+--------+---------+
| id | type  | table  | key  | rows | filtered| Extra  |         |
+----+-------+--------+------+------+---------+--------+---------+
|  1 | ALL   | o      | NULL | 298k |   11.11 | Using filesort   |
|  1 | ref   | u      | PRI  |    1 |  100.00 |                  |
|  1 | ref   | oi     | NULL | 3    |  100.00 |                  |
+----+-------+--------+------+------+---------+--------+---------+

Problem: type: ALL on the orders table = full table scan of 298k rows.

Add a composite index

-- Index covering both status and created_at (column order matters)
CREATE INDEX idx_orders_status_created
ON orders (status, created_at);

-- Or a covering index if this query runs frequently
CREATE INDEX idx_orders_status_created_user
ON orders (status, created_at, user_id);

After adding the index:

| type  | key                        | rows | Extra       |
| range | idx_orders_status_created  | 4800 | Using index |

Rows to scan dropped from 298k to 4800.

Rewrite the query

Still slow because of filesort. Add a subquery to limit rows before the JOINs:

SELECT o.id, o.created_at, u.name, u.email,
       COUNT(oi.id) as item_count,
       SUM(oi.price * oi.quantity) as total
FROM (
  SELECT id, created_at, user_id
  FROM orders
  WHERE status = 'completed'
    AND created_at >= '2026-01-01'
    AND created_at < '2026-04-01'
  ORDER BY created_at DESC
  LIMIT 50
) o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, u.name, u.email
ORDER BY o.created_at DESC;

This technique is called late row lookup — join only the 50 rows you actually need instead of the entire dataset.

Results

Total improvement: 60× faster, from 12 seconds down to 200ms.