Practical SQL Query Optimization: From Slow Scans to Efficient Indexes

As applications scale from hundreds of thousands to millions of records, poorly optimized database queries quickly become the primary bottleneck of modern web architectures. While hardware has gotten faster, an unindexed query forcing a full table scan across millions of rows will easily exhaust server CPU, lock connection pools, and degrade the user experience.

In this guide, we will explore practical, battle-tested strategies to diagnose and optimize slow SQL queries in relational database systems like MySQL and SQL Server, concluding with a real-world case study where a composite index slashed query latency by over 1,000%.

---

1. Stop Using SELECT * in Production

One of the most common habits in early-stage development is querying all columns:

-- Avoid in production
SELECT * FROM orders WHERE customer_id = 4502;

Why this hurts performance:

• I/O and Network Overhead: Retrieving large columns (TEXT, VARCHAR(MAX), blobs) transfers unnecessary bytes across database connection buffers.
• Prevents Covering Indexes: An index can only satisfy a query without reading the underlying clustered index (table heap) if all requested columns exist inside the index leaf nodes.

The Fix:

Only specify the exact columns your application logic or API needs:

SELECT order_id, total_amount, order_status, created_at 
FROM orders 
WHERE customer_id = 4502;

---

2. Diagnosing Bottlenecks with EXPLAIN

Before adding indexes blindly, always inspect how the database query planner executes your statement. In MySQL or PostgreSQL, prefix your query with EXPLAIN:

EXPLAIN SELECT order_id, total_amount 
FROM orders 
WHERE customer_id = 4502 AND order_status = 'COMPLETED';

Key Metrics to Inspect:

1. type: Look for ALL (full table scan). You want to see ref, eq_ref, or range.
2. rows: Represents the estimated number of rows examined. If this number is in the tens of thousands for a simple lookup, an index is missing.
3. key: Indicates which index the query planner selected. If NULL, no index was used.
4. Extra: Beware of Using filesort or Using temporary. This means the database is sorting records in memory or spilling intermediate batches to disk.

---

3. Designing Multi-Column (Composite) Indexes Correctly

When querying against multiple conditions in a WHERE clause, a composite index is often the best solution. However, column order inside the index matters due to the Leftmost Prefix Rule.

Suppose you run:

SELECT order_id, total_amount 
FROM orders 
WHERE customer_id = 4502 AND order_status = 'COMPLETED';

Creating an index as:

CREATE INDEX idx_orders_customer_status 
ON orders (customer_id, order_status);

How the Leftmost Prefix Rule Works:

• A search on customer_id alone will use this index.
• A search on customer_id AND order_status will use this index.
• A search on order_status alone CANNOT use this index effectively.

Rule of thumb: Put columns with higher selectivity (more unique values, like foreign keys) before columns with low cardinality (like status enums).

---

4. Beware of Functions on Indexed Columns (SARGability)

A frequent mistake that invalidates index usage is wrapping indexed columns in functions inside the WHERE clause:

-- Inefficient: Database must calculate DATE() for every row in the table
SELECT order_id FROM orders 
WHERE DATE(created_at) = '2026-09-01';

Because created_at is wrapped inside DATE(), the query engine cannot use the B-Tree index on created_at.

The SARGable Alternative:

Make the query SARGable (Search Argument Able) by comparing against an explicit range:

-- Efficient: Enables a fast index range scan
SELECT order_id FROM orders 
WHERE created_at >= '2026-09-01 00:00:00' 
  AND created_at < '2026-09-02 00:00:00';

---

5. Case Study: Slashing Chat Message Latency by 1,000%+ with a 3-Column Composite Index

To see how index architecture behaves under real production pressure, consider a real-world high-throughput chat platform (such as a multi-tenant WhatsApp CRM).

In customer messaging feeds, a common query retrieves the latest 100 messages for an active conversation thread:

SELECT id, conversation_id, sender_phone, content, timestamp
FROM messages
WHERE conversation_id = 14205 AND is_hidden = 0
ORDER BY timestamp DESC, id DESC
LIMIT 100;

The Problem Before the Index

Initially, the messages table only possessed a primary key on id. Under production load:

1. Full Table Scan (type: ALL): With hundreds of thousands of messages across all tenants, the database had to inspect every single row in storage to locate records where conversation_id = 14205.
2. Catastrophic Filesort (Using filesort): Because the query orders by timestamp DESC, id DESC, MySQL allocated space in sort_buffer_size. As conversation histories grew, the sort buffer overflowed, spilling intermediate sorting runs to temporary disk files.
3. The Polling Concurrency Trap: With multiple agents polling the conversation endpoint every 1,500ms, database CPU utilization pinned at 100%, and connection pools were rapidly exhausted. Query latency averaged 1,200ms to 2,500ms.

The Solution: The 3-Column Composite Index

We introduced an ordered composite index specifically targeting the filter equality, sort order, and tie-breaker:

CREATE INDEX messages_conversation_timestamp_id_idx 
ON messages (conversation_id, timestamp, id);

The Execution Plan Transformation

Execution Metric	Before (No Composite Index)	After (conversation_id, timestamp, id)	Improvement
Access Type (type)	ALL (Full Table Scan)	ref (Const point seek)	$O(N) \rightarrow O(\log N)$
Key Used	NULL	messages_conversation_timestamp_id_idx	Targeted Seek
Sorting Strategy	Using filesort (Disk Temp Tables)	None (In-tree index order)	0 bytes sort memory
Index Traversal	Scanned entire table	Backward index scan + Early exit	Stops after 100 rows
Query Latency	~1,800 ms	~1.8 ms	~1,000x faster (100,000%)

Why This Works Mechanically:

1. Equality Partitioning: The database navigates directly to conversation_id = 14205 in the B-Tree root in $O(\log N)$ time.
2. Zero-Cost Sorting: Because the B-Tree leaf nodes are already physically ordered by timestamp and id, the engine executes a backward index scan. It does not sort a single byte in memory.
3. Early Exit at LIMIT 100: Once the engine traverses the leaf nodes to collect 100 matching rows, it immediately terminates execution, ignoring the remaining millions of records in the table.

---

6. Prefer JOIN over Correlated Subqueries

Correlated subqueries execute once for every row evaluated by the outer query ($O(N \times M)$ complexity).

-- Slow correlated subquery: executes subquery for every customer row
SELECT c.customer_name,
       (SELECT SUM(o.total_amount) FROM orders o WHERE o.customer_id = c.customer_id) as total_spent
FROM customers c;

Converting this to a LEFT JOIN with GROUP BY allows the optimizer to execute an efficient hash join or index lookup:

-- Faster JOIN approach
SELECT c.customer_name, COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

---

Production Indexing Rules of Thumb

1. Order composite index columns strategically: Equality filters first (conversation_id = ?), followed by sort columns (timestamp DESC), followed by deterministic tie-breakers (id DESC).
2. Inspect EXPLAIN for Using filesort: If a high-frequency query shows Using filesort, your index is failing to satisfy the ORDER BY clause.
3. Beware of over-indexing: Every index speeds up SELECT statements but incurs an I/O write penalty on INSERT, UPDATE, and DELETE. Target your most frequent, latency-sensitive query paths first.

By designing composite indexes that satisfy both filtering predicates and sorting criteria, you can routinely eliminate disk-bound filesorts and turn multi-second bottlenecks into sub-millisecond lookups.