How to Analyze Query Performance
Query Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows Scanned | Few (100) | Many (100,000) |
| Cost | Low (10) | High (10,000) |
| Time | 0.01 sec | 5 sec |
4 rows
Check if your query is fast or slow.
EXPLAIN Command
Query Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows Scanned | Few (100) | Many (100,000) |
| Cost | Low (10) | High (10,000) |
| Time | 0.01 sec | 5 sec |
4 rows
Shows how database executes your query.
EXPLAIN SELECT * FROM students WHERE age > 20;Shows:
- Is index being used?
- How many rows scanned?
- Estimated cost
Look For
Query Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows Scanned | Few (100) | Many (100,000) |
| Cost | Low (10) | High (10,000) |
| Time | 0.01 sec | 5 sec |
4 rows
Good signs:
- Using index
- Few rows scanned
- Low cost
Bad signs:
- Sequential scan (no index)
- Many rows scanned
- High cost
Summary
Query Performance Indicators
| Indicator | Good | Bad |
|---|---|---|
| Scan Type | Index Scan | Sequential Scan |
| Rows Scanned | Few (100) | Many (100,000) |
| Cost | Low (10) | High (10,000) |
| Time | 0.01 sec | 5 sec |
4 rows
Use EXPLAIN to analyze queries Check if indexes are used Find slow queries and optimize them
Congratulations! You completed all SQL topics!