The Problem

One of the most-used reporting pages in our port operations ERP was taking 12 seconds to load. Users were complaining. Management was asking questions. The page showed container movement summaries with filters by date range, status, and port.

Step 1: Reproduce and Measure

First rule of optimization: measure before you change anything.

EXPLAIN SELECT c.container_no, c.status, m.moved_at, p.port_name
FROM containers c
JOIN movements m ON c.id = m.container_id
JOIN ports p ON m.port_id = p.id
WHERE m.moved_at BETWEEN '2024-01-01' AND '2024-03-31'
AND c.status = 'OUT'
ORDER BY m.moved_at DESC;

The EXPLAIN output showed type: ALL on the movements table — a full table scan across 2.4 million rows. No indexes being used on the date range or the join columns.

Step 2: The Indexes Were Wrong

The table had an index on container_id alone. But the query filters on moved_at AND joins on port_id. A single-column index on container_id was almost useless for this query pattern.

Step 3: Add Composite Indexes

-- For the date range filter + ordering
ALTER TABLE movements ADD INDEX idx_moved_at_status (moved_at, port_id);

-- For the join from containers side
ALTER TABLE movements ADD INDEX idx_container_port (container_id, port_id);

Step 4: Rewrite the Query

The original query used SELECT * in a subquery and then filtered the result. Rewriting to filter early:

SELECT c.container_no, c.status, m.moved_at, p.port_name
FROM movements m
INNER JOIN containers c ON c.id = m.container_id AND c.status = 'OUT'
INNER JOIN ports p ON p.id = m.port_id
WHERE m.moved_at BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY m.moved_at DESC
LIMIT 500;

Result

| Before | After | |--------|-------| | 12.2 seconds | 0.1 seconds | | Full table scan (2.4M rows) | Index range scan (~1,200 rows) |

320× faster.

Key Lessons

  1. Always run EXPLAIN first — never guess
  2. Composite indexes should match your WHERE clause column order
  3. Filter early — don't select everything and filter later
  4. Add a LIMIT to reporting queries; users don't need all 500,000 rows at once
  5. Watch for N+1 queries in ORM code — one lazy-loaded relationship can turn 1 query into 1,000