How to Optimize MySQL Queries

Speeding Up MySQL

Slow queries kill app performance. Here are proven techniques to optimize them.

1. Use EXPLAIN to Diagnose

EXPLAIN SELECT * FROM orders WHERE user_id = 5;
-- Shows if indexes are used and rows scanned

2. Add Indexes

CREATE INDEX idx_user ON orders(user_id);
-- Turns full scans into fast lookups

3. Select Only What You Need

-- ❌ Slow
SELECT * FROM users;
-- ✅ Fast
SELECT id, name FROM users;

4. Avoid Functions on Indexed Columns

-- ❌ Index not used
WHERE YEAR(created_at) = 2026
-- ✅ Index used
WHERE created_at >= '2026-01-01'

5. Use LIMIT for Large Results

SELECT * FROM logs ORDER BY id DESC LIMIT 100;

Optimization Checklist

• Index WHERE/JOIN/ORDER BY columns.
• Avoid SELECT *.
• Use EXPLAIN to find bottlenecks.
• Cache repeated queries.

FAQs

Why is my indexed query still slow?

The optimizer may skip the index for small tables or low selectivity. Check with EXPLAIN. More in our MySQL guides.

What is the slow query log?

A MySQL feature that logs queries exceeding a time threshold.