Most slow databases are not slow because of the hardware. They are slow because of a handful of habits that are easy to fix once you know them. Here are 15 practical SQL performance tuning tips you can apply today, each with a one line reason and a short snippet showing where it helps.
Treat this page as the fast reference. When you want the full reasoning behind each idea, read the deeper SQL performance tuning guide, and when you are ready to audit a real database top to bottom, run the SQL performance tuning checklist. The tips below apply to PostgreSQL, MySQL, and SQL Server alike; the syntax differs slightly but the principles do not.
The 15 tips
- Index the columns you filter and join on
- Stop writing SELECT star
- Write sargable predicates
- Read the plan with EXPLAIN
- Do your joins right and kill N+1
- Filter early and LIMIT
- Use covering and composite indexes wisely
- Keep statistics fresh
- Avoid implicit type conversions
- Prefer EXISTS over IN for big subqueries
- Paginate with keyset, not big OFFSET
- Batch large writes
- Right size your data types
- Cache with summary tables and views
- Monitor your slow queries
- Ranking the tips by impact and effort
1. Index the columns you filter and join on
An index turns a full table scan into a direct seek, so the query cost stops growing with the size of the table. Put an index on every column that shows up in a WHERE, a JOIN ... ON, or an ORDER BY, and never leave a foreign key unindexed.
-- The single highest return change in most databases
CREATE INDEX idx_orders_customer ON orders (customer_id);Indexing is the deepest topic in performance work. Markus Winand's Use The Index, Luke is the best free primer, and our own walkthrough on how SQL indexes improve performance covers the mechanics with worked examples.
2. Stop writing SELECT star
SELECT * drags back columns you never use, breaks covering indexes, adds network cost, and quietly changes behaviour when someone alters the table. Name the columns you actually need.
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id;SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;3. Write sargable predicates
A predicate is sargable when the engine can satisfy it with an index seek. Wrap the indexed column in a function or a cast and that ability disappears, because the engine must compute your expression for every row before it can compare. Keep the column bare and push the work onto the constant side.
-- Function on the column: the index on created_at is ignored
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;-- Range on the raw column: clean index seek
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';The same trap catches UPPER(email) = ..., arithmetic like price * 1.2 > 100, and leading wildcard LIKE '%term'. More rewrites live in our guide on how to write better SQL queries.
4. Read the plan with EXPLAIN
Never tune by guessing. EXPLAIN shows the strategy the optimizer intends, and EXPLAIN ANALYZE runs the query and reports the real row counts and timings so you can see where the cost is actually born.
-- PostgreSQL: plan, real timings, and buffer usage
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total FROM orders o
WHERE o.customer_id = 4217;
-- MySQL 8+: readable tree with real execution
EXPLAIN ANALYZE
SELECT id, total FROM orders WHERE customer_id = 4217;Watch for a sequential scan on a large filtered table, a wide gap between estimated and actual rows, or a sort spilling to disk. The official docs are worth bookmarking: the PostgreSQL performance tips chapter and the MySQL optimization reference both explain how to read their plans in depth.
Baseline first: save the plan and runtime before you change anything. Without a baseline you cannot prove a fix worked, and you will not notice when a later change makes things worse.
5. Do your joins right and kill N+1
The N+1 problem is the most common performance bug in application code: you fetch a list, then loop and run one more query per row. Two queries become 501 at 500 rows, each paying network latency. Fetch related data in a single round trip instead.
// 1 query for customers, then 1 per customer
customers = query("SELECT id, name FROM customers");
foreach (customers as c) {
c.orders = query("SELECT * FROM orders WHERE customer_id = ?", c.id);
}-- One join, or the ORM eager load option (include / with / preload)
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;6. Filter early and LIMIT
The fastest query touches the fewest rows. Apply the most selective conditions first so joins, sorts, and aggregates run on a small set, and never pull more rows to the application than you will display.
-- Narrow the set, then aggregate, then cap the output
SELECT customer_id, COUNT(*) AS n
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id
ORDER BY n DESC
LIMIT 50;7. Use covering and composite indexes wisely
A composite index serves multi column filters, and the column order matters: put equality columns first, then the range or sort column. A covering index that carries every column the query needs lets the engine answer from the index alone, with no trip back to the table.
-- Equality (customer_id) first, range (created_at) second
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, created_at);
-- Covering index: seek on the key, read total from the leaf
CREATE INDEX idx_orders_cover
ON orders (customer_id, created_at) INCLUDE (total);Do not over index, though. Every index taxes each write and consumes cache, so drop the ones that never get scanned. Our SQL indexes reference covers when each index type earns its keep.
8. Keep statistics fresh
The optimizer chooses plans from statistics about your data. After a bulk load or heavy churn those go stale and it guesses badly, picking a scan where a seek belonged. If estimated rows are far from actual rows, refresh the stats before touching anything else.
ANALYZE orders; -- PostgreSQL
VACUUM ANALYZE orders; -- Postgres, also cleans dead tuples
ANALYZE TABLE orders; -- MySQL
UPDATE STATISTICS orders; -- SQL Server9. Avoid implicit type conversions
Compare a text column to a number literal and the engine must cast every row before it can match, which kills the index and forces a scan. Match the literal type to the column type exactly.
-- account_no is VARCHAR; the number forces a per row cast
SELECT * FROM accounts WHERE account_no = 100482;SELECT * FROM accounts WHERE account_no = '100482';10. Prefer EXISTS over IN for big subqueries
When you only need to test that a match exists, EXISTS can stop at the first hit and usually plans better than a large IN (subquery). It also sidesteps the confusing NULL behaviour that NOT IN hides.
SELECT c.id, c.name FROM customers c
WHERE c.id IN (SELECT customer_id FROM orders);SELECT c.id, c.name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);11. Paginate with keyset, not big OFFSET
LIMIT 20 OFFSET 100000 makes the engine read and throw away 100,000 rows on every page, so deep pages get slower and slower. Keyset pagination stays constant time by remembering the last row seen.
SELECT id, created_at FROM orders
ORDER BY created_at
LIMIT 20 OFFSET 100000;-- Carry the last created_at from the previous page
SELECT id, created_at FROM orders
WHERE created_at > '2026-06-30 12:00:00'
ORDER BY created_at
LIMIT 20;12. Batch large writes
A thousand single row inserts pay the round trip and transaction overhead a thousand times. One multi row insert, or a chunked delete, pays it once and can be an order of magnitude faster.
-- One statement per row, a thousand round trips
INSERT INTO events (id, kind) VALUES (1, 'click');
INSERT INTO events (id, kind) VALUES (2, 'click');-- One statement, one round trip
INSERT INTO events (id, kind) VALUES
(1, 'click'), (2, 'click'), (3, 'view');13. Right size your data types
Narrower rows pack more records per page and keep more of the table in cache, so every read and every index gets cheaper. Pick the smallest type that correctly holds your values, store dates as date types, and keep the primary key compact.
-- Wasteful: BIGINT for a small range, DATETIME with no time, huge text
status_id BIGINT,
order_day DATETIME,
country VARCHAR(255)
-- Right sized
status_id SMALLINT,
order_day DATE,
country CHAR(2)| Type | Storage | Use it for |
|---|---|---|
SMALLINT | 2 bytes | Values up to about 32,000, like status codes |
INT | 4 bytes | Most identifiers and counts |
BIGINT | 8 bytes | Only when the range truly exceeds INT |
DATE | 3 to 4 bytes | Calendar days with no time component |
CHAR(2) | 2 bytes | Fixed width codes such as country |
14. Cache with summary tables and views
The fastest query is the one that never runs. Cache read heavy, rarely changing results in the application layer, and pre compute expensive aggregates into a summary table or a materialized view that you refresh on a schedule.
-- Pre compute a dashboard number once, read it thousands of times
CREATE MATERIALIZED VIEW daily_sales AS
SELECT created_at::date AS day,
SUM(total) AS revenue,
COUNT(*) AS orders
FROM orders
GROUP BY created_at::date;
-- Refresh on a schedule, off the request path
REFRESH MATERIALIZED VIEW daily_sales;Watch out: a cache serves stale data by design. Only cache what tolerates being a little out of date, and always have a clear rule for when it refreshes.
15. Monitor your slow queries
You cannot fix what you never see. Turn on the tools that rank queries by total cost so you tune the ones that hurt in aggregate, not the single outlier you happened to notice.
-- PostgreSQL: worst offenders by total time (needs pg_stat_statements)
SELECT substr(query, 1, 60) AS q,
calls,
round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;On MySQL enable the slow query log or read performance_schema; on SQL Server use Query Store. Rank by total time, which is calls × average, and start at the top. Our full performance tuning resource walks through setting each of these up.
Ranking the tips by impact and effort
All 15 tips help, but they do not pay equally. Across typical workloads, indexing and query rewrites return far more than configuration or hardware, and most of them cost little to apply. Spend your time from the top of this chart down.
The table below pairs each tip with a rough impact rating and how much effort it takes, so you can pick the quick wins first. Impact is how much a typical slow query improves; effort is how much work the change usually needs.
| Tip | Impact | Effort |
|---|---|---|
| 1. Index filter and join columns | Very high | Low |
| 2. Stop writing SELECT star | Medium | Low |
| 3. Write sargable predicates | High | Low |
| 4. Read the plan with EXPLAIN | High | Low |
| 5. Do joins right, kill N+1 | Very high | Medium |
| 6. Filter early and LIMIT | Medium | Low |
| 7. Covering and composite indexes | High | Medium |
| 8. Keep statistics fresh | Medium | Low |
| 9. Avoid implicit conversions | Medium | Low |
| 10. EXISTS over IN | Medium | Low |
| 11. Keyset pagination | High | Medium |
| 12. Batch large writes | High | Medium |
| 13. Right size data types | Low | Medium |
| 14. Cache and summary tables | Medium | Medium |
| 15. Monitor slow queries | High | Low |
The golden rule: change one thing at a time and re measure against your baseline. If you add an index, rewrite the predicate, and bump a config value all at once, you will never know which one helped.
Work these tips into your habits and most queries never become slow in the first place. When you want the reasoning in full, the performance tuning guide goes deeper on every point, and the tuning checklist gives you a repeatable routine to run against any database that is dragging.