How to Optimize Slow SQL Queries on Tables with Millions of Rows

When a query on a table with 10 million rows takes two minutes, the first instinct is to blame hardware. But database administrators and the Reddit r/SQL community know better. The bottleneck is almost never CPU or RAM — it is poor query design and missing indexes. This article breaks down the most common performance killers and the exact steps to eliminate them. Every recommendation originates from real-world threads on Ask Anything Wednesdays and veteran database engineers. No hype. No magic. Just mechanics.

Why Queries Slow Down on Large Tables

A full table scan reads every row. On a million-row table, that is a million disk pages loaded. If the table is 50 GB and the query needs only 500 rows, the database still chews through the entire 50 GB. (Insanity.) The root cause is a lack of indexing, poorly written JOINs, or SELECT * pulling unnecessary columns. The Reddit community consensus: “Measure first, then tune.” Jumping into index creation without understanding the query plan wastes time. The first tool is always EXPLAIN.

Step 1: Run EXPLAIN and Read the Tree

EXPLAIN shows the query execution plan. It reveals full table scans, join types (nested loop vs. hash), and estimated row counts. Look for these red flags:

  • “Seq Scan” on a large table — means no index used.
  • “Rows” estimated far off from actual rows — statistics are stale.
  • “Sort Method: external merge” — memory pressure.

The fix is not always another index. Sometimes a rewritten WHERE clause turns a sequential scan into an index scan. Example: WHERE YEAR(date) = 2024 prevents index usage. Replace with WHERE date >= '2024-01-01' AND date < '2025-01-01'. That one change alone can cut execution time from 45 seconds to 0.2 seconds.

Step 2: Add the Right Indexes (Not Just Any Index)

Indexes are not magic. A poorly chosen index slows writes and bloats storage. The Reddit community stresses compound indexes on columns used in WHERE and JOIN conditions. A single-column index on user_id is useful, but a compound index on (user_id, created_at) is better when queries filter on both. The order matters: put high-cardinality columns first. (Many beginners put low-cardinality columns first — mistake.)

For large tables, partial indexes can save space. Example: CREATE INDEX idx_active_users ON users (email) WHERE status = 'active'. Only rows that match the WHERE condition are indexed. Queries that filter by active users become lightning fast while the index stays small.

Step 3: Kill SELECT * and Functions in WHERE

SELECT * drags every column into memory. If the table has 50 columns, the database fetches all of them, even when only three are needed. This increases I/O and network transfer. (Don’t do it.) Replace with explicit column lists. The performance gain is linear to the number of columns eliminated.

Functions in WHERE clauses — WHERE LOWER(name) = 'john' — force a full table scan because the database must evaluate the function for every row. Workaround: use a generated column or store the lowercased value in a separate index. Another example: WHERE DATE(created_at) = '2024-01-01' — same problem. Use range conditions as shown earlier.

Step 4: Pagination — LIMIT/OFFSET Is a Trap

LIMIT 10 OFFSET 100000 is slow. The database still scans 100,010 rows to find the last 10. The Reddit community recommends keyset pagination (also called seek method) using a WHERE id > last_seen_id ORDER BY id LIMIT 10. This uses the primary key index and returns results in constant time regardless of offset. For unordered pagination, use a stable sort column with a unique index.

Step 5: Partition Large Tables by Date or Key

When a table exceeds 50 million rows, partitioning becomes necessary. Range partitioning by a date column lets queries that filter on date BETWEEN '2024-01-01' AND '2024-01-31' hit only one partition instead of the whole table. The overhead is minimal: partition pruning happens at query planning. The tradeoff is slightly more complex schema management. But for reporting tables with billions of rows, it is the only viable option outside of columnar storage.

Step 6: Materialized Views for Complex Aggregations

Aggregate queries on large tables — SELECT COUNT(*), AVG(price) FROM orders GROUP BY region — are slow because they scan the entire table. A materialized view stores the pre-computed result. Refresh it periodically. The cost shifts from every query to a scheduled maintenance window. Reddit users caution: do not overuse materialized views on rapidly changing data. The staleness tolerance must match the business need.

Measurement Before Tuning — The Golden Rule

The final and most important step is measurement. Run the query with EXPLAIN (ANALYZE, BUFFERS) to capture actual timing and buffer hits. Then apply one change at a time. Rerun EXPLAIN. Compare. The Reddit community has seen developers add five indexes at once and then complain that performance did not improve. (Of course it did not — they could not isolate the effect.) Use pg_stat_statements or equivalent to track cumulative execution time.

Conclusion

Optimizing slow SQL on large tables is not about guessing. It is a systematic process: measure, identify full scans, add compound indexes, rewrite queries to avoid functions, use keyset pagination, partition, and cache with materialized views. The database community has proven these steps work on tables with hundreds of millions of rows. (They work on 10 million too.) The difference between a query that runs in two minutes and one that finishes in 200 milliseconds is exactly this methodology. Start with EXPLAIN. End with a measured improvement.

No shortcuts. No silver bullets. Just better queries.