SQL Optimization Techniques for Large Tables

A query that runs fine on a 10,000-row test table can crawl once it hits a few million real rows — a common surprise once a system moves past its early stage. Most fixes fall into a handful of categories: making sure the database can actually use an index for your filter, not fetching more data than you need, and understanding what the query planner is actually doing instead of guessing. This guide covers the practical ones, each with a working example.

1. Index the Columns You Actually Filter, Join, and Sort On

Without an index, the database checks every row (a full table scan) to find matches. With the right index, it can jump straight to them.

CREATE INDEX idx_employees_salary ON employees(salary);

Indexing isn't free — every index speeds up reads but slows down writes, since it has to be maintained on every INSERT, UPDATE, and DELETE. For composite indexes, covering indexes, and how to read an index in an EXPLAIN plan, see the dedicated SQL indexing strategies guide.

2. Avoid SELECT *

Fetching every column costs more network transfer and memory than fetching only what you need — and if a covering index exists for your query, selecting a column outside it can force the database to fall back to a slower lookup.

-- Slower: fetches every column, even ones you'll never use
SELECT * FROM employees;

-- Faster: fetches only what the caller needs
SELECT name, salary FROM employees;

3. Write Sargable WHERE Clauses

"Sargable" (Search ARGument ABLE) means the database can use an index to evaluate the condition. Wrapping an indexed column in a function usually breaks that — the database has to compute the function for every row instead of doing an index lookup.

-- Not sargable: YEAR(join_date) must be computed for every row,
-- so an index on join_date can't be used
SELECT * FROM employees WHERE YEAR(join_date) = 2024;

-- Sargable: join_date is compared directly, so an index on it can be used
SELECT * FROM employees
WHERE join_date >= '2024-01-01' AND join_date < '2025-01-01';

The same applies to LOWER(email) = '...', col + 1 = 5, or any other transformation on the column itself — where possible, transform the value you're comparing against instead of the column.

4. Optimize JOINs

• Join on indexed columns — an unindexed JOIN condition forces a full scan of one side
• Filter before joining where possible, so the database has fewer rows to match
• Avoid joining tables you don't actually select or filter on

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2024-01-01';

For a full breakdown of JOIN types and when each one applies, see the SQL JOIN types guide, or diagram your own query's join graph with the SQL Query Visualizer.

5. Watch for the N+1 Query Problem

This is an application-code problem, not a query-syntax one — it happens when code loops over a result set and fires one additional query per row:

// N+1: 1 query for orders, then 1 more per order = 101 queries for 100 orders
const orders = await db.query('SELECT * FROM orders LIMIT 100');
for (const order of orders) {
  order.customer = await db.query('SELECT * FROM customers WHERE id = ?', [order.customer_id]);
}

// Fixed: 1 query total, using a JOIN
const orders = await db.query(`
  SELECT o.*, c.name AS customer_name
  FROM orders o JOIN customers c ON o.customer_id = c.id
  LIMIT 100
`);

A query that's individually fast can still make a page slow if it's run 100 times in a loop — this is one of the most common real-world performance bugs, and it doesn't show up in EXPLAIN because each individual query looks fine on its own.

6. Paginate Instead of Loading Everything

SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

LIMIT alone is fine for a first page, but plain LIMIT/OFFSET pagination gets slower the deeper you page, since the database still has to scan and discard every earlier row. For pages 50+ deep, see the keyset pagination pattern that stays fast regardless of page depth.

7. Consider Table Partitioning — But Only at Real Scale

Partitioning splits one large table into smaller physical pieces (commonly by date range), so queries that target one partition don't have to scan the whole table:

-- PostgreSQL example: declarative range partitioning
CREATE TABLE sales_2025 PARTITION OF sales
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

This is worth reaching for at genuinely large scale (tens of millions of rows and up) — below that, it adds real maintenance overhead (partition creation, and queries that don't filter on the partition key end up scanning every partition anyway) for a benefit indexing alone usually already covers. Try indexing and query rewrites first.

8. Read EXPLAIN Before Guessing

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

The output shows the query plan the database actually chose. The three things worth checking first: whether it says seq scan (full table scan) or index scan on a large table, the estimated row count (wildly wrong estimates often mean stale statistics), and, if your database supports it, running EXPLAIN ANALYZE to compare the estimate against what actually ran.

Common Mistakes

• Adding indexes reactively per-query instead of reviewing which columns are actually filtered/joined/sorted across the whole app
• Wrapping indexed columns in functions inside WHERE, silently disabling the index
• Using SELECT * out of habit in code that only needs 2-3 columns
• Not noticing an N+1 pattern because each individual query is fast
• Reaching for partitioning or caching before checking whether a missing index would fix it outright

Frequently Asked Questions