Stop Blaming Data Size: 90% of Slow SQLs Are Due to Poor Queries

1. Indexes Disabled by Bad Queries

Even when tables are small and indexes exist, applying functions to indexed columns (e.g., DATE(create_time) = '2026-03-01') prevents the optimizer from using the index, forcing a full scan. A better range query that compares the raw column values allows the index to be used efficiently.

SELECT * FROM orders WHERE DATE(create_time) = '2026-03-01';

SELECT * FROM orders WHERE create_time >= '2026-03-01 00:00:00' AND create_time < '2026-03-02 00:00:00';

Implicit type conversion also hurts index usage. Querying a string column with a numeric literal (e.g., WHERE phone = 13800138000) forces the database to convert types, reducing index selectivity.

SELECT * FROM user_profile WHERE phone = '13800138000';

Leading wildcard patterns ( LIKE '%coffee') cannot leverage B‑tree indexes because the engine cannot start from an ordered prefix, resulting in full scans.

SELECT * FROM product WHERE product_name LIKE '%coffee';

2. Fetching Too Much Unnecessary Data

Using SELECT * reads all columns, increasing I/O, network transfer, and possibly disabling covering indexes. Selecting only required columns dramatically reduces the workload.

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

Failing to limit result sets causes the database to retrieve massive rows before the application filters them. Adding appropriate LIMIT or pagination avoids this waste.

SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

3. Inefficient JOINs and Subqueries

Joining large tables without early filtering creates huge intermediate result sets, similar to pulling everyone into a meeting before asking who should stay. Optimizing by filtering first, adding proper indexes, and avoiding wide‑table joins improves performance.

Filter before join

Add suitable indexes on join columns

Avoid unnecessary wide‑table joins

Inspect the execution plan to verify the driving table

Correlated subqueries can be executed repeatedly for each outer row. Rewriting them as an aggregation followed by a join often yields a stable, faster plan.

SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt FROM users u;

SELECT u.id, t.order_cnt FROM users u LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id
) t ON u.id = t.user_id;

4. ORDER BY / GROUP BY Without Index Support

If the columns used in ORDER BY or GROUP BY lack a suitable composite index, the database must sort after scanning, consuming memory and possibly writing temporary files.

SELECT id, user_id, create_time FROM orders WHERE status = 'paid' ORDER BY create_time DESC;

Proper index ordering can let the engine produce sorted results directly, avoiding extra work.

5. Wrong Pagination Technique

Deep offset pagination ( LIMIT 100000, 20) forces the engine to skip a large number of rows before returning the desired page, leading to severe slowdown. Cursor‑based or key‑based pagination ( WHERE id > 100000 ORDER BY id LIMIT 20) is far more efficient.

SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

6. Not Looking at the Execution Plan

Before blaming hardware or architecture, examine EXPLAIN output. Key signals include type (full scan?), key (which index is used?), rows (estimated rows scanned), and Extra (e.g., Using filesort, Using temporary). Most performance issues become obvious within minutes.

7. When Data Volume Is Truly the Core Issue

Large, hot datasets, tables reaching tens of millions or billions of rows, heavy analytical queries, massive index size, or high write rates can make data volume a genuine bottleneck. However, many teams encounter “big‑data” symptoms far before reaching those scales due to poor SQL.

8. Five‑Step Slow‑SQL Diagnosis Checklist

Inspect the execution plan first.

Check whether query conditions invalidate indexes (functions, implicit casts, leading wildcards, bad index order).

Verify you aren't selecting unnecessary columns or rows.

Review JOIN, ORDER BY, and GROUP BY designs for inefficiencies.

Only after the query is healthy consider data‑volume or architectural solutions (sharding, caching, search engines).

By asking "Is this SQL written like a responsible query?" you often get closer to the truth than by asking "Is the table too big?"