SQL Query Optimization: 7 Steps to Fix Slow Queries in 2026

I still remember the morning my dashboard query went from 300 milliseconds to 37 seconds. No code change, no new data—just a silent regression that brought a production report to its knees. After a frantic cup of coffee and three false starts, I ran EXPLAIN ANALYZE. That one command showed me exactly where the engine was spinning its wheels: a full table scan on a table that had grown to 12 million rows. In 2026, with databases bigger and workloads more demanding, the same seven steps I used that morning still rescue slow queries every week. Here's how to optimize slow SQL queries basics and turn a sluggish query into a fast one.

Step 1: Diagnose the Bottleneck with EXPLAIN ANALYZE

Before you touch any index or rewrite a JOIN, you need to know why the query is slow. Guessing is the fastest path to wasted time. The tool for this is EXPLAIN ANALYZE (or EXPLAIN in older databases like MySQL 5.x). It doesn't just show you the query plan—it actually runs the query and reports real row counts, actual time per operation, and where the engine spends most of its cycles.

When I first used it on that 37-second query, the output showed a sequential scan on orders taking 34 seconds. The rest of the plan was trivial. That single line told me exactly where to focus. Without it, I might have rewritten JOINs or added indexes to the wrong columns. To use it yourself, prepend your slow query with EXPLAIN ANALYZE (MySQL 8.0.18+) or EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL. Look for high actual times on operations like Seq Scan, Nested Loop with many rows, or Sort steps. Those are your targets.

A common mistake is relying on estimated row counts from a plain EXPLAIN. Estimates can be wildly off—by factors of 10 or more. EXPLAIN ANALYZE shows you the truth. Once you know the bottleneck, you move to Step 2.

Step 2: Index the Filter and Join Columns

Indexes are the go-to fix for full table scans. But not all indexes are equal. The rule of thumb is simple: add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. For single-column filters, a B-tree index is usually enough. For queries filtering on multiple columns—like WHERE status = 'active' AND created_at > '2025-01-01'—a composite index on (status, created_at) can be dramatically faster than two separate indexes.

I once saw a query that filtered on user_id and date individually. The database picked one index, scanned the other condition, and still hit 500k rows. Adding a composite index on (user_id, date) cut the scan to 1,200 rows and dropped execution from 8 seconds to 90 milliseconds. The key is to order the columns by selectivity—most selective first—so the index narrows the result set as quickly as possible.

But indexes come with a cost. Every insert, update, or delete on a table with indexes updates those structures too. For write-heavy workloads, too many indexes can hurt overall throughput. My rule: add indexes only after profiling shows a real bottleneck, and monitor their usage with tools like pg_stat_user_indexes in PostgreSQL or sys.schema_unused_indexes in MySQL. If an index is never used, drop it.

Step 3: Rewrite Subqueries as JOINs or CTEs

Subqueries, especially correlated ones, are a classic performance trap. A correlated subquery executes once for each row in the outer query—row-by-row processing. For a table with a million rows, that means a million executions of the inner query. In my own setup, I once had a query like this:

SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders WHERE customer_id = orders.customer_id);

For each of 50,000 orders, it recalculated the average for that customer. That query took 23 seconds. Rewriting it as a JOIN with a grouped subquery (or a CTE) dropped it to under a second:

WITH customer_avg AS (SELECT customer_id, AVG(total) AS avg_total FROM orders GROUP BY customer_id)
SELECT o.* FROM orders o JOIN customer_avg ca ON o.customer_id = ca.customer_id WHERE o.total > ca.avg_total;

The CTE runs once, materializes the result, and the outer query joins against it. No row-by-row overhead. If you see DEPENDENT SUBQUERY in your EXPLAIN output, that's your cue to rewrite. Not all subqueries are bad—uncorrelated subqueries in WHERE or FROM clauses are often fine—but correlated ones are almost always worth replacing.

Step 4: Avoid SELECT * and Fetch Only What You Need

I know it's tempting to write SELECT * when you're prototyping. In production, it's a silent killer. It forces the database to read every column, including large TEXT or JSON fields, and transfer all that data over the network. For a report that only needs id, name, and total, pulling a 10 KB description column per row multiplies I/O and memory usage.

I worked on a query that fetched 200 rows from a table with a BLOB column containing 500 KB each. The query returned 100 MB of data. Switching to explicit columns reduced it to 60 KB—a 1,700× reduction—and the query time dropped from 4 seconds to 120 milliseconds. The fix is trivial: list only the columns you actually use. If you need many columns, consider a covering index that includes them so the database can satisfy the query from the index alone, avoiding a table lookup.

A practical habit: never use SELECT * in application code. In ad-hoc queries, it's fine for exploration, but for anything production-facing, be explicit. Your database and your users will thank you.

Step 5: Limit Searches with Partition Pruning (If You Have Large Tables)

If your tables are in the hundreds of millions or billions of rows, even indexed queries can be slow because the index itself is huge. Partitioning splits a table into smaller physical pieces based on a key—typically date. When your query filters on that key, the database can scan only the relevant partitions, a technique called partition pruning.

I maintain a table of web events partitioned by month on event_date. A query for last week's data scans exactly one partition (about 40 million rows) instead of the full 2 billion rows. Without partitioning, that query would take minutes; with it, it finishes in under a second. To set it up in MySQL, you'd write something like:

CREATE TABLE events (id INT, event_date DATE, ...) PARTITION BY RANGE (YEAR(event_date)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p2026 VALUES LESS THAN (2027)
);

Partitioning isn't free—it adds complexity to schema changes and can make some operations slower. Use it only when a table exceeds, say, 100 million rows and you have a clear partition key that matches your common WHERE clauses. For most smaller tables, indexes alone are sufficient.

Step 6: Tune Your Database Configuration

Sometimes the bottleneck isn't the query—it's the database server itself. Two configuration parameters have outsized impact on query speed: innodb_buffer_pool_size for MySQL, and shared_buffers for PostgreSQL. These control how much memory is used to cache data and indexes. If your buffer pool is too small, every query goes to disk. For a dedicated database server, set it to about 70-80% of available RAM. I once doubled the buffer pool from 4 GB to 8 GB on a 16 GB machine and saw average query times drop by 40%.

Another common misconfiguration is max_connections. Setting it too high (like 500) can overwhelm the server with connection overhead, causing context switching and slow response. Aim for a realistic number based on your application's concurrency—often 100-200 is plenty for most web apps. Also, in older MySQL versions, the query cache (query_cache_type) could help, but in MySQL 8.0 it's removed entirely because it caused contention. Focus on buffer pool and connection limits first.

Tuning these parameters is safe if you test on a staging server first. A simple change to my.cnf or postgresql.conf and a restart can yield immediate gains. Just remember to monitor the impact with tools like SHOW GLOBAL STATUS or pg_stat_bgwriter to ensure you're not over-allocating memory.

Step 7: Use Pagination Without OFFSET (Keyset Pagination)

If you've ever paginated through a large result set with LIMIT 50 OFFSET 10000, you've felt the pain. The database still scans all 10,050 rows just to skip the first 10,000. As the offset grows, the query gets slower and slower. Keyset pagination solves this by using a WHERE clause on a unique, indexed column to pick up where the last page left off.

Instead of:

SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 10000;

You do:

SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 50;

The database uses the index on id to jump directly to the starting point, scanning only 50 rows. In my experience, switching from OFFSET to keyset pagination on a query with 100,000 rows dropped page load times for deep pages from 2 seconds to under 10 milliseconds. The downside is that you can't jump to an arbitrary page number—you need to track the last seen key. But for infinite scroll,