SQL Performance Optimization Guide

A practical guide to optimizing SQL queries and database performance.

When optimizing SQL, focus on these goals:

• Reduce the number of scanned rows
• Reduce unnecessary data transfer
• Reduce expensive JOIN operations
• Avoid repeated queries
• Use indexes effectively
• Cache frequently accessed data

Before optimizing a query, inspect its execution plan using `EXPLAIN`.

`EXPLAIN` shows:

• Whether indexes are used
• Full table scans
• Join order
• Number of scanned rows
• Sorting operations
• Temporary tables

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 10
ORDER BY created_at DESC;

Queries should:

• Use indexes
• Scan as few rows as possible
• Avoid unnecessary sorting and temporary tables

Indexes are one of the biggest performance improvements in SQL.

Create indexes for columns frequently used in:

• `WHERE`
• `JOIN`
• `ORDER BY`
• `GROUP BY`

-- WHERE condition
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
 
-- ORDER BY
CREATE INDEX idx_orders_created_at
ON orders(created_at);
 
-- JOIN
CREATE INDEX idx_orders_user_id
ON orders(user_id);

If queries often filter by multiple columns:

SELECT *
FROM orders
WHERE customer_id = 10
AND STATUS = 'paid';

Use a composite index:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, STATUS);

• Indexes improve reads but slow down writes (`INSERT`, `UPDATE`, `DELETE`)
• Avoid adding indexes on every column
• Remove unused indexes

Avoid using:

SELECT *
FROM users;

• Transfers unnecessary data
• Uses more memory
• Prevents some index optimizations
• Slower network response

SELECT id, name, email
FROM users;

Only select the columns your application actually needs.

Fetching huge datasets is expensive.

SELECT *
FROM logs
ORDER BY created_at DESC;

SELECT id, message, created_at
FROM logs
ORDER BY created_at DESC
LIMIT 100;

• Reduces memory usage
• Faster response time
• Less network transfer

JOINs are expensive when tables are large.

SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

Recommended indexes:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Usually, primary keys are already indexed automatically.

Do not JOIN tables if the data is not needed.

SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

Avoid:

SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id;

The N+1 problem happens when an application executes one query to fetch a list, then runs additional queries for each item.

1 query to get users
+ 100 queries to get each user's orders
= 101 queries total

$users = User::all();
 
foreach ($users as $user) {
    echo $user->orders;
}

This may generate hundreds of SQL queries.

Load related data in a single query.

$users = User::with('orders')->get();

Benefits:

• Fewer database queries
• Faster response
• Reduced database load

Sometimes a JOIN is more efficient.

SELECT users.name, orders.total
FROM users
JOIN orders ON users.id = orders.user_id;

Use JOIN when:

• You only need combined result data
• You do not need full ORM relationship objects

Frequently executed queries should be cached.

• Dashboard statistics
• Product lists
• Configuration data
• Frequently accessed reports

Application
    ↓
Check cache
    ↓
If cache exists → return cached data
If not:
    Query database
    Store result in cache
    Return result

• Redis
• Memcached

• Reduces database load
• Faster response times
• Better scalability

Normalization improves data consistency, but too many JOINs can hurt performance.

Sometimes it is beneficial to combine data from multiple tables into one table.

Instead of:

orders
users
products
addresses

You may store some duplicated data directly in `orders`:

orders:
  customer_name
  customer_email
  shipping_address

This reduces expensive JOIN operations.

• Faster reads
• Simpler queries
• Fewer JOINs

• Data duplication
• More storage usage
• Harder updates
• Risk of inconsistent data

Only denormalize after identifying real performance bottlenecks.

Avoid loading all rows at once.

SELECT *
FROM products;

SELECT id, name, price
FROM products
LIMIT 20 OFFSET 0;

SELECT id, name, price
FROM products
WHERE id > 100
ORDER BY id
LIMIT 20;

Keyset pagination is usually faster for large datasets.

Enable slow query logging in production.

• Queries taking too long
• Queries scanning too many rows
• Missing indexes
• Frequent repeated queries

• MySQL Slow Query Log
• PostgreSQL `pg_stat_statements`
• APM tools (New Relic, Datadog)

Before deploying a query, check:

• Did you run `EXPLAIN`?
• Are indexes used correctly?
• Are indexes missing on `WHERE`, `JOIN`, or `ORDER BY` columns?
• Are you avoiding `SELECT *`?
• Are you limiting returned rows?
• Are JOINs necessary?
• Is there an N+1 query problem?
• Can caching help?
• Can denormalization reduce heavy JOINs?
• Is pagination implemented?

• First: measure with `EXPLAIN`
• Second: add proper indexes
• Third: reduce unnecessary data
• Fourth: optimize query patterns
• Fifth: cache expensive operations

Do not optimize blindly — optimize based on actual bottlenecks.