Why is Your Query Slow? A 60-Second Guide to EXPLAIN

You have a query that takes 3 seconds to run. Why? Guessing doesn't work. MySQL tells you exactly what it is doing if you ask it.

The command is EXPLAIN.

How to use it: Run your slow query in your SQL client (TablePlus, PHPMyAdmin, etc), but put the word EXPLAIN in front of it.

                    EXPLAIN SELECT * FROM orders WHERE status = 'pending';
                

What to look for: Look at the type column in the result.

• ALL: 🚨 Bad. This is a "Full Table Scan." MySQL is reading every single row to find your data. You are missing an Index.

• ref / eq_ref: ✅ Good. It is using an Index to jump straight to the data.

The Fix: If you see ALL, add an index to the column in your WHERE clause.

                    CREATE INDEX idx_status ON orders(status);