Database Query Optimization: Speed Up Your App With Better Queries

Find Slow Queries

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
mysqldumpslow /var/log/mysql/slow.log | head -20

Using EXPLAIN

Run EXPLAIN before any slow SELECT to see how MySQL executes it. Look for type=ALL (full table scan) — this means no index is being used. Adding an appropriate index changes type to ref or eq_ref.

Creating Composite Indexes

CREATE INDEX idx_user_status ON orders(user_id, status, created_at);
-- Covers: WHERE user_id=? AND status=? ORDER BY created_at

FAQ

Can too many indexes hurt performance?

Yes. Each index slows INSERT/UPDATE/DELETE operations. Add indexes for frequently queried columns but remove unused ones.