Last updated
Common Performance Anti-Patterns
- SELECT * — retrieve only the columns you need
- No WHERE clause — always filter data when possible
- Functions on indexed columns — use range conditions instead
- Correlated subqueries — rewrite as JOINs with aggregation
- N+1 queries — use JOINs to fetch related data in one query
- Missing indexes on JOIN and WHERE columns — add targeted indexes
- Implicit type conversions — match literal types to column types
- Cartesian products — always specify JOIN conditions
- LIKE '%prefix' — leading wildcards prevent index usage
- OR conditions on different columns — consider UNION ALL instead
Examples
Example 1: SELECT * Anti-Pattern
Original query:
SELECT * FROM orders WHERE customer_id = 123;
Issues detected:
⚠ SELECT * retrieves all 24 columns including large
TEXT and BLOB fields not needed for this use case.
Optimized query:
SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = 123;
Performance impact:
- Reduces data transfer by ~80%
- Allows covering index to satisfy query without
accessing main table rows
- Reduces memory usage in application layer
Example 2: Missing Index on WHERE Clause Column
Original query:
SELECT * FROM users WHERE email = 'user@example.com';
EXPLAIN output (without index):
type: ALL (full table scan)
rows: 1,250,000
Extra: Using where
Issues detected:
✗ Full table scan on 1.25M rows
✗ No index on email column
Recommendation:
CREATE INDEX idx_users_email ON users(email);
EXPLAIN output (with index):
type: ref
rows: 1
Extra: Using index condition
Performance improvement: ~1,250,000× fewer rows examined
Query time: 2,400ms → 0.8ms
Example 3: Function on Indexed Column
Original query:
SELECT * FROM orders
WHERE YEAR(created_at) = 2024;
Issues detected:
✗ YEAR() function prevents index usage on created_at
✗ Full table scan required even with index present
Optimized query:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';
Why it works:
The range condition allows the database to use
the index on created_at for an efficient range scan.
Performance improvement: Full scan → index range scan