Why 90% of Slow SQL Queries Aren’t Caused by Large Data Volumes

1. Not a Large Table, but an Index That’s Been Disabled

Applying functions to indexed columns (e.g., DATE(create_time)) prevents the optimizer from using the index, forcing a full scan. Use range conditions directly on the column, such as

WHERE create_time >= '2026-03-01 00:00:00' AND create_time < '2026-03-02 00:00:00'

, to let the index work.

2. Implicit Type Conversion

Comparing a string column with a numeric literal (e.g., WHERE phone = '13800138000' vs. a number) may trigger costly type conversion, reducing index utilization. Such subtle mistakes often cause intermittent slow queries.

3. Leading Wildcard LIKE

Patterns like LIKE '%coffee' cannot use a B‑tree index because the prefix is unknown, resulting in full scans. If this pattern is frequent, consider redesigning the search, using a full‑text index, or a dedicated search service.

2. Not Too Much Data, but Too Much Unnecessary Data

1. SELECT *

Fetching all columns reads data that may never be used, increasing I/O, network traffic, and possibly invalidating covering indexes. Selecting only needed columns (e.g.,

SELECT order_id, amount, status FROM orders WHERE user_id = 1001

) can dramatically improve performance.

2. Missing LIMIT

Returning massive result sets when only a small page is needed wastes resources. Apply LIMIT, proper pagination, or cursor‑based paging instead of pulling tens of thousands of rows and trimming them in application code.

3. Not Data Size, but Overly Complex JOINs or Subqueries

1. Small‑Table‑Driven Large‑Table Scans

If filter conditions are applied after a large join, the database may generate huge intermediate results. Optimize by filtering early, adding appropriate indexes on join columns, avoiding wide joins, and verifying the driving table via the execution plan.

2. Inefficient Subqueries

Correlated subqueries like (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) can be executed repeatedly for each outer row. Rewriting as an aggregation followed by a join stabilizes performance.

4. Not Table Size, but ORDER BY / GROUP BY Without Index Support

1. ORDER BY

If the ordering columns lack a suitable composite index, the database must sort after filtering, consuming memory and possibly creating temporary files. Providing an index that matches the ORDER BY sequence lets the DB walk the index directly.

2. GROUP BY

Grouping benefits from an index that aligns with the grouping columns; otherwise, temporary tables and extra sorting increase CPU usage. Index order therefore influences the query’s fate.

5. Not Data Volume, but Wrong Pagination Technique

Deep pagination using LIMIT offset, size (e.g., LIMIT 100000, 20) forces the engine to skip many rows, making later pages slower. Cursor‑ or primary‑key‑based pagination ( WHERE id > 100000 ORDER BY id LIMIT 20) is far more efficient.

6. Not a Bad Database, but Ignoring the Execution Plan

Many performance debates revolve around guesses about data size, version, or hardware. Running EXPLAIN quickly reveals whether indexes are used, how many rows are scanned, and if filesort or temporary tables appear. This diagnostic step prevents unnecessary architectural changes.

7. When Does Data Volume Truly Matter?

Large data sets do impact performance in scenarios such as hot‑data concentration, tables reaching tens of millions or billions of rows, heavy analytical queries, massive index sizes reducing cache hits, and frequent writes increasing index maintenance cost. However, most teams encounter slow SQL well before reaching these scales.

8. Five‑Step Checklist for Troubleshooting Slow SQL

1. Examine the Execution Plan

Confirm index usage, row estimates, and presence of temporary tables or filesort.

2. Identify Index‑Breaking Conditions

Function operations

Implicit type conversions

Leading wildcard LIKE

Improper composite index order

3. Reduce Columns and Rows Retrieved

Avoid SELECT *, narrow result sets, and filter early.

4. Review JOIN, ORDER BY, and GROUP BY Design

Ensure joins are filtered early and ordering/grouping can leverage indexes.

5. Consider Data‑Scale Solutions Only After Query Health

If the query is well‑written and indexed, then explore sharding, caching, or search‑engine offloading.

Conclusion

The most misleading excuse for a slow query is “the data set is too large.” In reality, the root causes are often index loss, over‑selecting, wrong sorting, bad pagination, or never looking at the execution plan. Ask whether the SQL behaves like a “well‑behaved” query before blaming data size.