10 SQL Query Optimization Tips Every Developer Should Know

# 10 SQL Query Optimization Tips Every Developer Should Know

TL;DR -- Key Takeaways

>

- Stop selecting columns you don't need. SELECT * forces the database to read and transfer data your application throws away.

- Indexes are the single highest-leverage optimization. One well-placed index can reduce query time from seconds to milliseconds.

- Use EXPLAIN ANALYZE before guessing. The query plan tells you exactly where time is spent.

- Batch your writes, paginate with cursors, and replace correlated subqueries with JOINs or CTEs.

- Tools like QueryDeck surface these bottlenecks visually in SQL notebooks so you can fix them faster.

Every application hits a point where the database becomes the bottleneck. Response times creep up, connection pools fill, and users start complaining. The cause is almost always the same: SQL queries that worked fine on small data sets but collapse under production load.

The good news is that sql query optimization follows a small set of repeatable patterns. Most slow queries share common problems, and most fixes are straightforward once you know where to look. This guide covers 10 practical tips with real before-and-after SQL examples that apply to PostgreSQL and MySQL alike.

1. Stop Using SELECT *

The problem

SELECT * retrieves every column from the table, including large text fields, blobs, and columns your application never reads. This wastes I/O, memory, and network bandwidth. It also prevents the database from using covering indexes, which can serve a query entirely from the index without touching the table.

Bad example

SELECT * FROM orders WHERE customer_id = 1042;

If the orders table has 25 columns including a notes TEXT field and a receipt_pdf BYTEA field, every row returned carries all of that data -- even if your frontend only displays the order ID, date, and total.

Good example

SELECT id, order_date, total_amount, status
FROM orders
WHERE customer_id = 1042;

Why it matters

Selecting only the columns you need reduces the amount of data the database reads from disk and sends over the network. If an index covers all the requested columns, PostgreSQL and MySQL can satisfy the query from the index alone -- a covering index scan that avoids heap lookups entirely. On a table with millions of rows, this difference is measurable in both latency and CPU cost.

2. Add the Right Indexes

The problem

A missing index forces the database to perform a sequential scan (Seq Scan in PostgreSQL, Full Table Scan in MySQL), reading every row in the table to find matches. On small tables this is fine. On a table with 10 million rows, it can take seconds.

Bad example

-- No index on orders.customer_id
SELECT id, order_date, total_amount
FROM orders
WHERE customer_id = 1042;

The query plan shows a Seq Scan with an estimated cost proportional to the total number of rows.

Good example

-- Create the index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Same query, now uses an index scan
SELECT id, order_date, total_amount
FROM orders
WHERE customer_id = 1042;

For queries that filter and sort, a composite index often performs even better:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);

Why it matters

Indexes turn O(n) scans into O(log n) lookups. A B-tree index on customer_id lets the database jump directly to the matching rows instead of scanning millions. Composite indexes serve multi-column filters and ORDER BY clauses in a single index traversal. The trade-off is write overhead: every INSERT and UPDATE must also update the index. In practice, for read-heavy workloads the gains far outweigh the cost.

Tip: Not sure which indexes are missing? Run your slow query through EXPLAIN ANALYZE and look for Seq Scans on large tables. Our guide to EXPLAIN ANALYZE in PostgreSQL walks through reading query plans step by step.

3. Use EXPLAIN ANALYZE Before Guessing

The problem

Developers often guess at the cause of a slow SQL query. They add indexes randomly, rewrite JOINs, or increase timeouts. Without looking at the actual execution plan, they are optimizing blind.

Bad example

-- "It's probably the JOIN that's slow, let me rewrite it"
-- (30 minutes of refactoring later, no improvement)

Good example

EXPLAIN ANALYZE
SELECT o.id, o.order_date, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.order_date > '2026-01-01';

The output tells you exactly what happened:

Nested Loop  (cost=0.85..124.30 rows=12 width=52) (actual time=0.045..0.312 rows=14 loops=1)
  -> Index Scan using idx_orders_status_date on orders o  (cost=0.43..45.20 rows=12 width=20) (actual time=0.022..0.085 rows=14 loops=1)
        Index Cond: ((status = 'pending') AND (order_date > '2026-01-01'))
  -> Index Scan using customers_pkey on customers c  (cost=0.42..6.44 rows=1 width=36) (actual time=0.012..0.012 rows=1 loops=14)
        Index Cond: (id = o.customer_id)
Planning Time: 0.189 ms
Execution Time: 0.398 ms

Now you know the JOIN is not the problem -- both sides use index scans. If performance is still too slow, you can look elsewhere with confidence.

Why it matters

EXPLAIN ANALYZE is the single most important tool for database query optimization. It replaces speculation with data. Look for: Seq Scans on large tables, large gaps between estimated and actual row counts, sorts that spill to disk, and nested loops with high loop counts.

QueryDeck's visual EXPLAIN ANALYZE renders these query plans as interactive diagrams, color-coded by time spent on each node. Instead of parsing indented text output, you can spot the bottleneck at a glance.

4. Replace Correlated Subqueries with JOINs

The problem

A correlated subquery runs once for every row in the outer query. If the outer query returns 10,000 rows, the subquery executes 10,000 times. This is one of the most common causes of slow SQL in production.

Bad example

SELECT
  c.id,
  c.name,
  (SELECT MAX(o.order_date)
   FROM orders o
   WHERE o.customer_id = c.id) AS last_order_date
FROM customers c;

For each customer row, PostgreSQL executes the subquery against the orders table. With 50,000 customers, that is 50,000 subquery executions.

Good example

SELECT
  c.id,
  c.name,
  MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Why it matters

The JOIN version lets the database scan the orders table once and aggregate in a single pass instead of re-querying for every row. The performance difference grows linearly with the number of rows in the outer query. In most cases, converting a correlated subquery to a JOIN or a CTE is the fastest path to a dramatic speedup.

5. Use CTEs for Readability, but Know When to Inline

The problem

Common Table Expressions (CTEs) improve readability by naming intermediate result sets. However, in older versions of PostgreSQL (before 12) and in MySQL, CTEs are materialized as temporary tables, which can prevent the optimizer from pushing predicates into them.

Bad example (materialized CTE when not needed)

WITH all_orders AS (
  SELECT * FROM orders
)
SELECT id, order_date, total_amount
FROM all_orders
WHERE customer_id = 1042
  AND status = 'shipped';

In MySQL and PostgreSQL < 12, the database materializes the entire orders table into a temporary result set before applying the WHERE filter. That kills performance on large tables.

Good example

-- PostgreSQL 12+: the optimizer inlines this automatically
WITH recent_shipped AS (
  SELECT id, order_date, total_amount
  FROM orders
  WHERE customer_id = 1042
    AND status = 'shipped'
)
SELECT * FROM recent_shipped;

Or simply avoid the CTE when it does not add clarity:

SELECT id, order_date, total_amount
FROM orders
WHERE customer_id = 1042
  AND status = 'shipped';

Why it matters

CTEs are valuable for breaking complex queries into readable steps, especially multi-level aggregations and recursive queries. But they are not free. Know your database version. In PostgreSQL 12+ you can use WITH ... AS MATERIALIZED or WITH ... AS NOT MATERIALIZED to control this behavior explicitly. In MySQL 8.0+, CTEs are generally inlined by the optimizer, but verify with EXPLAIN.

6. Fix N+1 Queries at the Application Layer

The problem

N+1 queries happen when application code fetches a list of records and then runs a separate query for each record to load related data. This is the most common sql performance tuning problem in ORM-heavy applications.

Bad example (pseudocode + SQL)

# Fetch all customers
customers = db.query("SELECT * FROM customers WHERE region = 'EU'")

# For each customer, fetch their orders (N queries)
for customer in customers:
    orders = db.query(
        "SELECT * FROM orders WHERE customer_id = %s",
        customer.id
    )

If the first query returns 200 customers, this fires 201 total queries. Each round-trip to the database adds latency.

Good example

-- Single query with a JOIN
SELECT c.id, c.name, o.id AS order_id, o.order_date, o.total_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.region = 'EU';

Or, if you need separate result sets, use a single IN query:

SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE region = 'EU'
);

Why it matters

The overhead of N+1 queries is not the SQL execution itself -- individual queries may each take 1 ms -- but the round-trip latency. With 200 queries at 2 ms network round-trip each, you spend 400 ms on network alone. A single JOIN or IN query eliminates that overhead entirely. Most ORMs provide eager loading mechanisms (e.g., includes in Rails, select_related in Django, eager in Eloquent) specifically to solve this.

7. Paginate with Cursors, Not OFFSET

The problem

LIMIT ... OFFSET pagination forces the database to scan and discard all rows before the offset. Page 100 of a 50-row-per-page result requires scanning 5,000 rows to discard the first 4,950. The deeper the page, the slower the query.

Bad example

-- Page 100: scans 5,000 rows, returns 50
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 50 OFFSET 4950;

Good example

-- Cursor-based: always scans only 50 rows
SELECT id, title, created_at
FROM articles
WHERE created_at < '2026-05-20T10:15:00Z'
ORDER BY created_at DESC
LIMIT 50;

The application passes the created_at value from the last item on the previous page. The database uses the index to jump directly to the right position.

Why it matters

Cursor-based pagination (also called keyset pagination) maintains constant performance regardless of how deep into the result set the user navigates. OFFSET pagination degrades linearly. For APIs and infinite-scroll UIs, cursor-based pagination is the standard best practice. The trade-off is that you lose the ability to jump to an arbitrary page number, but most modern UIs do not need that.

8. Use Batch Operations for Writes

The problem

Inserting or updating rows one at a time incurs the overhead of query parsing, planning, and transaction management for each statement. Inserting 10,000 rows as 10,000 individual INSERTs is orders of magnitude slower than a single batch.

Bad example

-- 10,000 individual INSERT statements
INSERT INTO events (user_id, event_type, created_at) VALUES (1, 'click', NOW());
INSERT INTO events (user_id, event_type, created_at) VALUES (2, 'view', NOW());
INSERT INTO events (user_id, event_type, created_at) VALUES (3, 'click', NOW());
-- ... 9,997 more

Good example

-- Single multi-row INSERT
INSERT INTO events (user_id, event_type, created_at) VALUES
  (1, 'click', NOW()),
  (2, 'view', NOW()),
  (3, 'click', NOW()),
  -- ... batch up to 1,000 rows per statement
  (1000, 'view', NOW());

For updates, use a VALUES list with a JOIN:

-- PostgreSQL: batch update with a VALUES list
UPDATE products AS p
SET price = v.new_price
FROM (VALUES
  (101, 29.99),
  (102, 49.99),
  (103, 19.99)
) AS v(id, new_price)
WHERE p.id = v.id;

-- MySQL: batch update with INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO products (id, price) VALUES
  (101, 29.99),
  (102, 49.99),
  (103, 19.99)
ON DUPLICATE KEY UPDATE price = VALUES(price);

Why it matters

Batching reduces the number of round-trips between application and database, reduces WAL (Write-Ahead Log) overhead in PostgreSQL, and allows the database to optimize the write path. A common guideline is to batch 500-1,000 rows per statement. Going higher can increase lock contention and memory usage, so test for your specific workload.

9. Use Parameterized Queries (Prepared Statements)

The problem

Constructing SQL by concatenating strings creates two problems: SQL injection vulnerabilities and missed plan caching. When every query has different literal values embedded in the string, the database treats each as a unique statement and must parse and plan it from scratch.

Bad example

# String concatenation -- vulnerable and slow
query = f"SELECT * FROM users WHERE email = '{user_email}'"
db.execute(query)

Every call generates a distinct query string. The database cannot reuse cached execution plans. And if user_email contains '; DROP TABLE users; --, you have a much bigger problem.

Good example

# Parameterized query -- safe and fast
query = "SELECT id, name, email FROM users WHERE email = $1"
db.execute(query, [user_email])

-- PostgreSQL: explicit prepared statement
PREPARE user_by_email (text) AS
  SELECT id, name, email FROM users WHERE email = $1;

EXECUTE user_by_email('alice@example.com');

-- MySQL: prepared statement
PREPARE user_by_email FROM
  'SELECT id, name, email FROM users WHERE email = ?';

SET @email = 'alice@example.com';
EXECUTE user_by_email USING @email;

Why it matters

Parameterized queries let the database parse the query once and reuse the execution plan for subsequent calls with different parameter values. This eliminates repeated parsing overhead for queries that run thousands of times per second. It also completely prevents SQL injection, which remains one of the most common web application vulnerabilities. Every modern database driver and ORM supports parameterized queries -- there is no reason not to use them.

10. Cache Expensive Query Results

The problem

Some queries are inherently expensive: aggregations over millions of rows, complex multi-table JOINs for dashboard reports, or full-text search scoring. Running these on every request wastes database resources when the underlying data changes infrequently.

Bad example

-- Runs on every dashboard page load, takes 800ms
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Good example

Use a materialized view (PostgreSQL) or a summary table (MySQL):

-- PostgreSQL: materialized view
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Refresh on a schedule (e.g., every hour via cron or pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- MySQL: summary table updated by a scheduled event
CREATE TABLE monthly_revenue (
  month DATE PRIMARY KEY,
  total_orders INT,
  revenue DECIMAL(12,2),
  avg_order_value DECIMAL(10,2)
);

-- Populate/refresh via INSERT ... ON DUPLICATE KEY UPDATE

For application-level caching, store query results in Redis or Memcached with a TTL that matches your data freshness requirements.

Why it matters

Caching moves expensive computation from the hot path. A materialized view in PostgreSQL stores precomputed results on disk and serves reads like a regular table -- sub-millisecond for the same query that took 800 ms live. The CONCURRENTLY option allows refreshes without locking reads. In MySQL, a summary table with a scheduled event achieves the same result. Application-level caching with Redis adds another layer for data that can tolerate staleness.

Putting It All Together: A SQL Optimization Checklist

When you encounter a slow SQL query, work through these steps in order:

1. Measure first: Run EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL) to get the actual execution plan.
2. Check for missing indexes: Look for Seq Scans or Full Table Scans on tables with more than a few thousand rows.
3. Eliminate SELECT *: Request only the columns your application uses.
4. Flatten correlated subqueries: Replace them with JOINs or CTEs.
5. Fix N+1 patterns: Use eager loading or batch queries in your ORM.
6. Switch to keyset pagination: Replace OFFSET with cursor-based pagination for deep page access.
7. Batch writes: Group INSERTs and UPDATEs into multi-row operations.
8. Use prepared statements: Let the database cache execution plans.
9. Cache expensive results: Materialized views, summary tables, or application-level caching.
10. Re-measure: Run EXPLAIN ANALYZE again to confirm the improvement.

How QueryDeck Helps You Optimize SQL Queries

QueryDeck is the database client that knows your project, built for app developers who care about query performance. With ORM auto-detection and SQL notebooks, two features are particularly relevant to sql query optimization:

Visual EXPLAIN ANALYZE: QueryDeck renders PostgreSQL and MySQL execution plans as interactive diagrams. Nodes are color-coded by execution time, so the slowest operation jumps out immediately. You can click any node to see row estimates vs. actuals, buffer usage, and I/O stats. This is dramatically faster than reading raw EXPLAIN ANALYZE output in a terminal. Learn more in our complete EXPLAIN ANALYZE guide.

AI query assistant: QueryDeck's AI assistant can analyze a slow query, suggest index additions, and propose rewritten SQL. Bring your own API key (OpenAI, Anthropic, or a local Ollama model) so there are no per-query costs. It is like having a DBA on call, integrated directly into your database client.

If you are working with PostgreSQL or MySQL and want to go from identifying a slow query to fixing it in minutes instead of hours, give QueryDeck a try.

SQL best practices do not require exotic techniques. The 10 tips in this guide -- selecting specific columns, adding targeted indexes, reading execution plans, replacing subqueries, fixing N+1 patterns, using cursor pagination, batching writes, parameterizing queries, and caching results -- will resolve the vast majority of performance problems you encounter in production. Start with EXPLAIN ANALYZE, fix the biggest bottleneck, measure again, and repeat.