Taming Black‑Swan Database Failures: Pagination, Join, and Count(*) Optimizations

Introduction

Drawing on Taleb's definition of a "black‑swan"—an unpredictable event with massive impact that seems inevitable in hindsight—the author links such events to database stability, noting that many companies aim for high‑availability SLAs (e.g., nines of uptime) but still suffer from sudden failures.

Analysis of Database‑Related Black‑Swan Events

Three typical scenarios illustrate the problem:

Program errors that cause a query intended to fetch a single row to request millions of rows, filling sessions and triggering an avalanche effect.

Normal pagination requests that, when executed on large tables at high frequency, produce slow queries and the same avalanche.

Third‑party integrations that pull full data sets instead of incremental changes, generating massive slow queries.

All three share the common root cause of slow, high‑frequency queries.

Case 1: Large Pagination Query Optimization

Business logic often pulls order data in batches using LIMIT N,M. When N grows large (e.g., for big merchants), performance degrades sharply.

Original query:

SELECT * FROM so WHERE 1 AND `bb`='xxxxx' AND `cc` IN ('5') AND `dd` IN ('0','1','2','3') ORDER BY id DESC LIMIT 70000,100;

Optimization methods:

Leverage the ordering of a composite index (e.g., created_time) instead of ORDER BY id, because MySQL can use only one effective index.

Apply a delayed‑join (covering index) strategy: first retrieve primary‑key IDs using the ordered index, then fetch full rows by primary key, avoiding full index scans.

Optimized query example:

SELECT a.* FROM so a JOIN (SELECT id FROM so WHERE 1 AND `bb`='xxxxx' AND `cc` IN ('5') AND `dd` IN ('0','1','2','3') ORDER BY created_time DESC LIMIT 70000,101) b ON a.id=b.id;

Additionally, the author suggests shifting from full‑batch pulls to push‑based incremental updates, reducing load and bandwidth.

Case 2: Join Query Optimization

The business needs to count orders per merchant using a join between so and oi tables:

SELECT COUNT(o.ono) AS num FROM so o, oi i WHERE o.ono=i.ono AND o.kid='xxxx' AND i.gid='yyyy';

Index definitions (relevant parts):

KEY idx_sid (idx_sid) USING BTREE,
KEY idx_ono (idx_ono) USING BTREE,
KEY idx_created (created),
key idx_kid(kid,cc,created_time)

Optimization principles:

Reduce nested‑loop iterations by driving the smaller result set.

Ensure the inner loop’s WHERE clause uses the most selective index.

Guarantee that join columns on the driven table are indexed.

If indexing is impossible, tune join_buffer_size appropriately.

Applied changes:

Replace idx_ono with a covering index idx_gid_ono(gid, ono) to avoid row look‑ups.

Prefer key‑value lookups over joins for high‑traffic C‑end services; migrate aggregation to Elasticsearch when feasible.

Case 3: Concurrent COUNT(*) Optimization

High concurrency on COUNT(*) caused ~200 simultaneous processes, degrading overall performance.

Recommendations:

Ensure the WHERE clause can use the most selective index.

Avoid concurrent counts at the application layer; cache results when possible.

The author references a separate article on COUNT(*) vs COUNT(col) for deeper insight.

Conclusion

After a month of focused slow‑query remediation, the team reduced slow‑query volume by roughly 90 % and shrank the slow‑log file from 1 MB to about 4 KB, dramatically lowering system risk and improving user experience.