Practical SQL Query Optimization: From Slow Scans to Efficient Indexes

As applications scale to millions of records, the efficiency of database interaction becomes the most critical determinant of system throughput. When queries lack proper indexing, the database engine is forced to perform a full table scan—a process that reads every record in a table to identify matches. This architectural shortcoming creates a cascade of performance failures that manifest as systemic bottlenecks.

The primary technical consequences of unoptimized queries include:

• CPU Exhaustion: Performing full table scans and memory-intensive sorting operations (filesorts) forces the database engine to consume excessive CPU cycles, which can drive utilization to near 100% under moderate load.
• Connection Pool Exhaustion: Slow queries hold database connections open for extended durations. This leads to connection starvation, where new requests are queued or rejected because the connection pool has reached its maximum capacity.
• I/O and Network Saturation: Querying unnecessary columns (e.g., using SELECT *) generates significant network overhead and I/O pressure, as the database must pull large blobs or text fields from disk into buffer pools, often causing cache eviction of more relevant data.

To diagnose these issues, engineers should leverage the EXPLAIN command. If the execution plan shows a type: ALL or indicates Using filesort, the query is likely bypassing available indices or forcing the database to materialize temporary results on disk. These disk-based operations are orders of magnitude slower than in-memory index traversals.

Mitigating these bottlenecks requires designing composite indexes that align with both filtering and sorting requirements. By ordering columns in an index to match the Leftmost Prefix Rule—placing highly selective equality filters before columns used in ORDER BY clauses—you allow the database to execute targeted index seeks rather than scanning entire data sets. This strategy, combined with writing SARGable (Search Argument Able) queries that avoid wrapping indexed columns in functions, effectively transforms multi-second latency into sub-millisecond execution times.

When a query uses SELECT *, the database engine must read every column from the matching rows, even if the application only needs a subset. This creates two forms of unnecessary overhead:

• I/O and network load: Large columns such as TEXT, VARCHAR(MAX), or BLOBs are transferred from storage to the client buffer, consuming disk bandwidth and network packets that do not contribute to the result set.
• Loss of covering‑index potential: A covering index can satisfy a query entirely from the index leaf nodes, avoiding a lookup in the clustered table. If the query requests columns that are not present in the index, the engine must perform a bookmark lookup, turning a fast index seek into a costly table read.

Instead of SELECT *, specify only the columns required by the business logic, for example:

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

To verify whether a query is using an index or falling back to a full table scan, prepend EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to the statement. The output contains several fields that reveal the execution plan:

• type – Look for ALL, which indicates a full table scan. Preferred values are ref, eq_ref, or range.
• rows – The estimated number of rows examined; a high value for a simple lookup suggests a missing index.
• key – The name of the index chosen; NULL means no index was used.
• Extra – Flags such as Using filesort or Using temporary signal that the engine must sort results in memory or on disk, which is often avoidable with a proper covering index.

Example diagnosis:

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

If the plan shows type: ALL and Extra: Using filesort, a composite index like CREATE INDEX idx_orders_customer_status ON orders (customer_id, order_status); can turn the access method into an eq_ref seek and eliminate the filesort because the index already orders the rows as required.

By limiting column selection and confirming the plan with EXPLAIN, engineers can reduce I/O, enable covering indexes, and avoid expensive full scans and external sorts, leading to predictable latency at scale.

The Leftmost Prefix Rule governs how a B‑Tree composite index can be used by the query optimizer. An index on columns (A, B, C) can satisfy predicates that reference the leftmost contiguous subset of those columns: A alone, A and B together, or A, B, and C. Any predicate that starts with column B or C without referencing A cannot leverage the index efficiently, often resulting in a full table scan or a costly filesort.

Because the rule is order‑sensitive, placing columns with the highest selectivity—those that filter out the largest portion of rows—at the beginning of the index maximizes its utility. High‑selectivity columns typically have many distinct values (e.g., foreign‑key identifiers) compared with low‑cardinality columns such as status flags or boolean fields.

-- Example: high‑selectivity column first
CREATE INDEX idx_orders_customer_status
ON orders (customer_id, order_status);

In this example, customer_id is a foreign key with many unique values, while order_status is an enum with few distinct values. The index can be used for:

• Searches on customer_id alone.
• Searches on customer_id AND order_status.
• Range scans that start with customer_id and then order by order_status.

Conversely, a query that filters only on order_status would not benefit from this index because the leftmost column (customer_id) is missing from the predicate.

When designing multi‑column indexes, follow these practical steps:

• Identify equality predicates. Columns used with “=”, “IN”, or “IS NULL” should appear first.
• Rank columns by selectivity. Place the most selective equality columns before less selective ones.
• Include sorting columns. If the query orders by additional columns, append them after the equality columns to avoid “Using filesort”.
• Validate with EXPLAIN. Confirm that the plan shows ref or range access and no filesort.
• Avoid over‑indexing. Each index adds write overhead; prioritize the most frequent, latency‑sensitive queries.

By respecting the Leftmost Prefix Rule and deliberately ordering columns from high‑selectivity to low‑selectivity, engineers can create composite indexes that dramatically reduce row scans, eliminate unnecessary sorting, and achieve sub‑millisecond query latency for high‑throughput workloads.

When a column is indexed, the optimizer can navigate the B‑Tree structure directly if the predicate references the column exactly. Wrapping that column in a function (e.g., DATE(created_at), UPPER(email)) forces the engine to evaluate the function for every row, which breaks the index’s ability to perform a range or point lookup. This loss of SARGability typically results in a full table scan (type = ALL) and higher CPU, I/O, and lock contention.

Why functions defeat indexes

• The function must be computed row‑by‑row, preventing the use of the pre‑sorted B‑Tree.
• Execution plans show type: ALL or Extra: Using where instead of ref or range.
• Even with a covering index, the function forces a lookup of the base table because the index does not store the computed value.

SARGable alternatives

Replace the function call with an explicit range that the index can evaluate directly. For a datetime column, compare the raw value against a lower and upper bound that represent the same logical condition.

-- Non‑SARGable (index on created_at is ignored)
SELECT order_id
FROM orders
WHERE DATE(created_at) = '2026-09-01';

-- SARGable equivalent
SELECT order_id
FROM orders
WHERE created_at >= '2026-09-01 00:00:00'
  AND created_at <  '2026-09-02 00:00:00';

Similar patterns apply to other data types:

• String case conversion: replace UPPER(email) = 'USER@EXAMPLE.COM' with a case‑insensitive collation or store a normalized column.
• Numeric truncation: replace FLOOR(price/10) = 5 with price BETWEEN 50 AND 59.99.

By expressing predicates as direct comparisons, the optimizer can use ref, eq_ref, or range access types, resulting in logarithmic (O(log N)) lookups instead of linear scans. This practice is essential for high‑throughput services where millions of rows are queried frequently, as demonstrated by the dramatic latency reduction achieved when converting a non‑SARGable date filter to an explicit range in production workloads.

Optimizing high-throughput database operations requires a deep understanding of B-Tree traversal and query execution planning. In scenarios involving chat platforms, performance bottlenecks frequently originate from inefficient sorting strategies and unnecessary full table scans during data retrieval.

When executing queries that filter by a specific thread and sort by time, such as WHERE conversation_id = ? ORDER BY timestamp DESC, id DESC LIMIT 100, databases often default to ALL scan types if an appropriate index is absent. This forces the engine to load massive datasets into the sort_buffer_size. When this buffer is insufficient, the system resorts to filesort, spilling intermediate results to temporary disk files, which degrades latency from milliseconds to seconds.

The implementation of a 3-column composite index—structured as (conversation_id, timestamp, id)—addresses these issues through three mechanical optimizations:

• Equality Partitioning: By placing the high-selectivity conversation_id first, the B-Tree allows for direct $O(\log N)$ point seeks to the specific conversation thread.
• Zero-Cost Sorting: Because the B-Tree leaf nodes are physically ordered by timestamp and id, the database engine can perform a backward index scan. This eliminates the filesort operation entirely, as the requested order is already represented by the structure of the index itself.
• Early Exit Traversal: By satisfying the LIMIT clause through the index, the query engine terminates execution immediately upon retrieving the 100 required rows, rather than scanning the remainder of the dataset.

In a production high-throughput chat environment, this approach reduced query latency from approximately 1,800ms to 1.8ms. Engineers should verify these optimizations by prefixing queries with EXPLAIN to ensure the type field shifts from ALL to ref and the Extra field no longer reports Using filesort. This transition from disk-bound sorting to in-tree index navigation is the most effective method for scaling read-intensive message feeds.

In relational database management, managing complexity effectively requires choosing between correlated subqueries and JOIN operations. A correlated subquery behaves iteratively, executing the inner query once for every row processed by the outer query. This results in $O(N \times M)$ complexity, which scales poorly as the dataset grows, frequently leading to high CPU utilization and connection pool exhaustion.

Conversely, replacing correlated subqueries with a LEFT JOIN accompanied by a GROUP BY clause allows the query optimizer to leverage hash joins or efficient index lookups. By transforming iterative execution into a set-based operation, you significantly reduce the computational overhead. For example, aggregating order totals for customers should be structured as follows:

• Avoid: Correlated subqueries that force individual row-by-row processing.
• Implement: A LEFT JOIN with SUM() and GROUP BY, allowing the engine to traverse the index structure systematically rather than repeatedly scanning tables.

Balancing read speed against write penalties is critical when implementing indexing strategies. While indexes are necessary to avoid full table scans—which incur heavy I/O costs—every additional index introduces a persistent write penalty on INSERT, UPDATE, and DELETE operations. To optimize this balance, follow these rules of thumb:

• Composite Index Ordering: Always prioritize equality filters first, followed by sort columns, and finally deterministic tie-breakers (e.g., primary keys). This satisfies the Leftmost Prefix Rule and prevents disk-bound filesort operations.
• Audit Execution Plans: Use EXPLAIN to identify Using filesort or Using temporary markers. If these appear, the query planner is bypassing your index for sorting, indicating the index needs adjustment to match the ORDER BY clause.
• Prioritize Latency-Sensitive Paths: Avoid over-indexing tables with high transactional volume. Focus indexing efforts on your most frequent, latency-sensitive query paths to maximize performance gains while minimizing the impact on data modification throughput.