10 Powerful SQL Optimization Techniques to Boost Query Performance

1. Introduction

SQL is the core language for database interaction, and inefficient queries (such as full‑table scans, missing indexes, or complex nested subqueries) can cause CPU spikes, memory exhaustion, and severe latency, especially with millions of rows. A single well‑tuned query can shrink execution time from minutes to milliseconds, lower server load, and improve user experience.

2. Practical Optimization Cases

2.1 Replace DISTINCT with GROUP BY when possible

Before

<code>SELECT DISTINCT customer_id FROM orders</code>

After

<code>SELECT customer_id FROM orders GROUP BY customer_id</code>

GROUP BY can be more efficient than DISTINCT, particularly when the grouped column is indexed, because DISTINCT often creates a temporary table.

2.2 Use UNION ALL instead of OR on indexed columns

Before

<code>SELECT * FROM products WHERE category = 'Electronics' OR category = 'Books'</code>

After

<code>SELECT * FROM products WHERE category = 'Electronics'
UNION ALL
SELECT * FROM products WHERE category = 'Books'</code>

UNION ALL allows each condition to use its own index, avoiding a full‑table scan that OR may cause.

2.3 Avoid functions on indexed columns

Before

<code>SELECT * FROM users WHERE UPPER(username) = 'JOHNDOE';</code>

After

<code>-- Store usernames in a consistent case or search the raw value
SELECT * FROM users WHERE username = 'JohnDoe';</code>

Functions break index ordering; on MySQL 8.0.13+ you can create a functional index if needed.

<code>CREATE INDEX idx_name ON t_person ((UPPER(username)));
</code>

2.4 Replace IN with EXISTS for large subqueries

Before

<code>SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM blacklist);</code>

After

<code>SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM blacklist b WHERE b.customer_id = o.customer_id);</code>

EXISTS performs better with large or correlated subqueries.

2.5 Use covering indexes

Before

<code>SELECT order_id, customer_id FROM orders WHERE order_date >= '2025-05-10';</code>

After

<code>CREATE INDEX idx_date_oid_cid ON orders(order_date, order_id, customer_id);</code>

A covering index contains all columns needed, allowing an index‑only scan.

2.6 Partition large tables

When a table may reach tens of millions of rows, partition it by a suitable key (e.g., date):

<code>CREATE TABLE t_orders (
  id INT NOT NULL,
  custom_id VARCHAR(255),
  order_no VARCHAR(255),
  address VARCHAR(255),
  total_amount DECIMAL(9,2),
  order_date DATETIME NOT NULL,
  PRIMARY KEY (id, order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(order_date) (
  PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
  PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
  ...
  PARTITION p202512 VALUES LESS THAN ('2026-01-01'),
  PARTITION pfuture VALUES LESS THAN MAXVALUE
);</code>

Partitioning speeds up time‑range queries when the partition key matches the query condition.

2.7 Use UNION ALL instead of OR (alternative example)

Before

<code>SELECT * FROM products WHERE category = 'Electronics' OR price > 2000;</code>

After

<code>SELECT * FROM products WHERE category = 'Electronics'
UNION ALL
SELECT * FROM products WHERE price > 2000;</code>

Separating the conditions lets each use its own index, often yielding 10‑100× speedups on large tables.

2.8 Replace NOT IN with NOT EXISTS

Before

<code>SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);</code>

After

<code>SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);</code>

NOT EXISTS is not affected by NULLs in the subquery and performs better on large datasets.

2.9 Limit ORDER BY results

Before

<code>SELECT * FROM products ORDER BY price DESC;</code>

After

<code>SELECT * FROM products ORDER BY price DESC LIMIT 10;</code>

LIMIT avoids sorting the entire table when only the top N rows are needed.

2.10 Replace subqueries with JOINs

Before

<code>SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = 1);</code>

After

<code>SELECT e.* FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.active = 1;</code>

JOINs allow the optimizer to apply more efficient strategies such as hash or merge joins.