Five database optimizations that turned 10-second queries into milliseconds

Pagination is one of the most common query patterns in online applications. On small tables, it rarely becomes a bottleneck. On large order tables in a distributed database, pagination queries face a series of challenges: unstable index selection, expensive table lookups, and wasted partition scans that create long-tail slow queries. As infrastructure demands grow, these problems only get harder, as the broader infrastructure bottleneck analysis explains.

This analysis draws from real production cases to share five optimization insights for paginated queries on large order tables in PolarDB-X, Alibaba Cloud's distributed SQL database. The strategies are built around a two-level partitioning scheme: first-level KEY partitioning by user ID, and second-level RANGE partitioning by a time field, an architecture common in financial, e-commerce, and SaaS platforms.

The optimizations cover the full chain from partition pruning, index utilization, and table-lookup reduction to stability assurance. Before diving in, a note: the table schemas and SQL statements here have been anonymized and do not represent real business data, though the performance numbers are from actual user scenarios. For context on how Alibaba is investing in AI infrastructure, see Alibaba's $53 billion infrastructure plan.

The anatomy of a slow pagination query

A typical order table in PolarDB-X is wide, 60+ columns, with covering indexes designed to reduce table lookups. The partitioning scheme ensures all data for a given user lands in the same first-level partition (by user ID), then subpartitions by date (by a pt timestamp column).

Consider a query that fetches recently modified orders for a large tenant:

SELECT * FROM t_order
WHERE uid = 12345678
  AND pt >= '2024-08-01 00:00:00.000'
  AND update_ts > '2024-11-13 14:20:00.000'
  AND update_ts < '2024-11-13 14:25:00.000'
  AND origin != 33
ORDER BY id DESC
LIMIT 0, 100;

The pt >= '2024-08-01' condition forces the query to scan four subpartitions: p202408, p202409, p202410, and p202411. But the update_ts filter restricts results to orders modified in the last few minutes, which are almost always recent orders, meaning the actual qualifying rows all land in the p202411 partition. The other three partitions are scanned for nothing. On a large tenant's partition, each wasted scan essentially reads through all the data in that partition, becoming a long-tail slow query that determines overall latency.

Insight 1: partition pruning by filter predicates

When a filter column correlates with the partition key (here, recently modified orders tend to have recent pt values), the database can exploit that correlation to skip unnecessary subpartitions. PolarDB-X's optimizer can dynamically prune partitions by recognizing that the effective range of pt implied by the update_ts filter is much narrower than the literal pt >= '2024-08-01' range. In the real user scenario, this optimization dropped a query from 5 seconds to 0.01 seconds.

Insight 2: partition pruning by sort column

A more common scenario is when the user's SQL contains only uid as a filter, no time predicates at all. Without partition pruning, the query must scan all subpartitions under the corresponding first-level partition. Under the original merge logic, all partitions' physical SQL statements must complete before the merge sort can proceed, meaning the slowest partition (often an older one with massive data) determines overall latency.

PolarDB-X addresses this with dynamic pruning based on the sort column. Because the sort column (id or update_ts) is roughly monotonic across time (newer partitions have higher IDs or later timestamps), the optimizer can stop scanning partitions as soon as enough rows are found. This reduced a query from 3 seconds to 0.05 seconds in production.

Insight 3: index ordering for OR/IN conditions

The key to fast pagination on row stores is early termination: rather than filtering first and then sorting to pick the top K, the query should exploit index ordering to filter and pick the top K simultaneously. Equality predicates naturally do this, but OR conditions break index ordering.

The fix is to split OR/IN conditions into multiple ordered subqueries, then merge:

SELECT * FROM (
    (SELECT * FROM t_swap WHERE asset_a = 'TOKEN26'
     ORDER BY id LIMIT 100)
    UNION
    (SELECT * FROM t_swap WHERE asset_b = 'TOKEN50'
     ORDER BY id LIMIT 100)
) t
ORDER BY id
LIMIT 100;

Each branch uses its own index for early termination. In the user scenario, this transformed a 10-second query into a 0.001-second one.

Insight 4: reduce table lookups with late materialization

For wide tables (60+ columns), SELECT * with table lookups is extremely expensive. Covering indexes help, but they consume significant storage. When covering indexes aren't feasible, late materialization is the answer: first retrieve only the primary keys through a covering index (fetching only the LIMIT number of rows), then do a pinpoint primary-key lookup for those few rows.

SELECT t_order0.*
FROM (
    SELECT id, uid, pt
    FROM t_order 
    WHERE uid = 1
      AND channel_id = 0
      AND sub_id IN (0)
      AND id > 123456789
    ORDER BY update_ts DESC
    LIMIT 20
) AS t3
INNER JOIN t_order AS t_order0
  ON t3.id = t_order0.id
  AND t3.uid = t_order0.uid
  AND t3.pt = t_order0.pt
ORDER BY t_order0.update_ts DESC
LIMIT 20;

This cut a query from 10 seconds to 0.6 seconds. Beyond SQL-level optimizations, PolarDB-X also optimizes table lookups at the storage engine level (Guess Primarykey Pageno and physical addressing) to reduce I/O amplification and improve cache hit rates. For related work on optimizing inference infrastructure, see Unsloth's kernel improvements for 5x faster LLM training.

Insight 5: deterministic index selection

For large order tables, memory is never enough. If the wrong index is chosen, large volumes of data are loaded into the buffer pool, polluting the cache and triggering cascading failures. The database's index selection must follow a deterministic methodology that aligns with AI-recommended index designs.

The standard checklist: index the sort column (avoid filesort), place WHERE equality columns before ORDER BY columns in composite indexes, use covering indexes when possible, put high-selectivity columns first, avoid functions on indexed columns, and maintain type consistency. When the database's behavior matches the recommended index design, stability is ensured. In production, PolarDB-X maintained stable index selection while a competing distributed database showed unstable behavior, leading to a 4K peak QPS (with crashes) versus 60K (linearly scalable) for PolarDB-X. This kind of stability is crucial for systems like those described in the multi-agent coordination challenges.

Summary table of real-world improvements

The core ideas can be summarized as: prune aggressively, exploit index ordering for early termination, minimize table lookups with covering indexes or late materialization, and ensure index selection is deterministic. For teams running large order tables on distributed databases, applying these five lessons systematically can prevent the most common pagination performance pitfalls. As GPU infrastructure becomes a similar bottleneck for AI workloads, the lesson about deterministic behavior applies broadly, as shown in the GPU hopping integration analysis.