SQL Query Optimization — 10 Tips to Write Faster Queries

1. Use EXPLAIN Before Touching Anything

The single most important optimization tool in SQL is EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL). It shows you exactly how the database executes your query — which indexes it uses, how many rows it scans, and where the bottlenecks are. Never optimize blind. Always EXPLAIN first.

MySQL / PostgreSQL / T-SQL

-- MySQL & PostgreSQL
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- PostgreSQL with actual timing
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- T-SQL (SQL Server)
SET SHOWPLAN_TEXT ON;
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
SET SHOWPLAN_TEXT OFF;

2. Index Columns in WHERE, JOIN, and ORDER BY

Indexes are the most powerful performance lever in SQL. A well-placed index can turn a 10-second full table scan into a 5-millisecond index lookup. The columns you should index are those used in:

• WHERE clauses (filtering)
• JOIN conditions (table relationships)
• ORDER BY (sorting)

Before (no index — full scan)

SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

Without an index on user_id, the database scans all 5 million rows.

After (composite index)

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Now the same query uses the index — reads only matching rows
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

3. Stop Using SELECT *

SELECT * is the most common performance anti-pattern in SQL. It forces the database to read every column from disk — including large TEXT, BLOB, or JSON columns you do not need. It also prevents index-only scans: even when an index covers all the columns you actually need, SELECT * forces a trip to the main table for the extra columns.

Bad

SELECT * FROM users WHERE id = 42;

Good

SELECT id, name, email FROM users WHERE id = 42;

This single change can reduce I/O by 90% on tables with wide rows. Always list exactly the columns you need.

4. Filter Early — Use WHERE, Not HAVING

WHERE filters rows before aggregation. HAVING filters after. If you put a condition in HAVING that could have been in WHERE, the database does extra work on rows it will later discard.

Bad — filter in HAVING

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING department != 'Interns' AND AVG(salary) > 50000;

Good — filter early in WHERE

SELECT department, AVG(salary)
FROM employees
WHERE department != 'Interns'
GROUP BY department
HAVING AVG(salary) > 50000;

The second query excludes Interns rows before calculating averages — less data to aggregate, faster result.

5. Use Explicit JOINs, Not Implicit WHERE Joins

Implicit joins (comma-separated tables in FROM + conditions in WHERE) are harder for both humans and optimizers to parse. Explicit JOIN syntax separates join conditions from filter conditions, making the query's intent clear.

Bad — implicit join

SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND o.status = 'completed';

Good — explicit JOIN

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

While most modern optimizers produce the same plan for both, explicit JOINs prevent accidental cross joins and make query intent obvious during code review.

6. Avoid Functions on Indexed Columns in WHERE

Wrapping an indexed column in a function prevents the database from using the index. The optimizer cannot "see through" the function to the underlying column.

Bad — function on indexed column

-- Cannot use index on created_at
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-14';

-- Cannot use index on name (case-insensitive search in MySQL)
SELECT * FROM users WHERE LOWER(name) = 'alice';

Good — compare against the column directly

-- Range query uses the index on created_at
SELECT * FROM orders
WHERE created_at >= '2026-07-14'
  AND created_at < '2026-07-15';

-- Use a function-based index (PostgreSQL) or computed column (T-SQL)
CREATE INDEX idx_users_name_lower ON users(LOWER(name));
SELECT * FROM users WHERE LOWER(name) = 'alice';

7. Use EXISTS Instead of IN for Subqueries

EXISTS stops scanning as soon as it finds a match. IN often materializes the entire subquery result set before checking. For large subquery results, EXISTS is significantly faster.

Slower — IN with large subquery

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Faster — EXISTS stops at first match

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 1000
);

The EXISTS version can use a semi-join optimization and stops scanning orders as soon as it finds one qualifying row per user. The IN version may scan the entire orders subquery.

8. Be Careful with OR Conditions

OR can prevent index usage because the database may need to scan for two separate conditions. Rewrite OR using UNION or IN when possible.

Problematic — OR prevents index use

SELECT * FROM users WHERE id = 42 OR email = 'alice@example.com';

Better — UNION uses both indexes

SELECT * FROM users WHERE id = 42
UNION
SELECT * FROM users WHERE email = 'alice@example.com';

The UNION version can use the index on id for the first part and the index on email for the second — each subquery gets an optimal plan. Use UNION ALL if you know the results will not overlap (faster, no dedup step).

9. Always Use LIMIT with ORDER BY

When you only need the top N rows, LIMIT (or TOP in T-SQL) lets the database stop early. Without it, the database sorts the entire result set even if you only want the first 10 rows.

Without LIMIT — database sorts everything

SELECT * FROM orders ORDER BY total DESC;

With LIMIT — database stops early

SELECT * FROM orders ORDER BY total DESC LIMIT 10;

When combined with an index on total, the database can read the index in reverse order and stop after 10 rows — it never touches the main table for the other millions of rows.

10. Batch INSERT and UPDATE Statements

Running 1,000 individual INSERT statements means 1,000 round trips to the database, 1,000 transactions, and 1,000 index updates. Batching them into a single statement reduces the overhead dramatically.

Bad — 1000 individual inserts

INSERT INTO logs (message) VALUES ('row 1');
INSERT INTO logs (message) VALUES ('row 2');
-- ... 998 more ...

Good — single batch insert

INSERT INTO logs (message) VALUES
  ('row 1'),
  ('row 2'),
  ('row 3'),
  -- ... up to 1000 rows per batch
  ('row 1000');

FAQ — SQL Query Optimization