Even with optimal indexes in place, poorly constructed SQL syntax can prevent the query optimizer from leveraging them. In database engineering, these bad syntax habits are known as SQL Anti-Patterns.
Mastering SARGable (Search Argument Able) query design guarantees maximum index utilization.
Anti-Pattern 1: Wrapping Indexed Columns in Functions (Non-SARGable)
When you wrap an indexed column in a function, MySQL cannot use the index because the index contains raw column values, not function results!
1-- ANTI-PATTERN (Forces Full Table Scan!):
2SELECT*FROM orders
3WHEREYEAR(order_date)=2026;
4
5-- BEST PRACTICE (SARGable: Preserves raw indexed column!):
6SELECT*FROM orders
7WHERE order_date >='2026-01-01 00:00:00'
8AND order_date <'2027-01-01 00:00:00';
Anti-Pattern 2: Implicit Type Conversion
If you compare a numeric column to a string, or a string column to an integer, MySQL converts the column value for every row, disabling the index:
1-- Schema: phone_number is VARCHAR(20) with an index
2-- ANTI-PATTERN (Column is cast to numeric on every row!):
3SELECT*FROM customers WHERE phone_number =9876543210;
4
5-- BEST PRACTICE (Quotes preserve string comparison!):
6SELECT*FROM customers WHERE phone_number ='9876543210';
Anti-Pattern 3: Wildcard Prefixes (LIKE '%term')
1-- ANTI-PATTERN (Cannot navigate B+Tree from the root!):
2SELECT*FROM products WHERE sku LIKE'%PROD';
3
4-- BEST PRACTICE (Prefix wildcard utilizes B+Tree range!):
5SELECT*FROM products WHERE sku LIKE'PROD%';
6-- Or use Full-Text Search for arbitrary substring matching!
Anti-Pattern 4: SELECT * in Production Applications
Using SELECT *:
1
Destroys Covering Index optimizations by demanding all unindexed columns.
2
Increases network bandwidth consumption between DB and backend API servers.
3
Wastes application memory parsing unused columns (like large BLOB or TEXT fields).
1-- ANTI-PATTERN:
2SELECT*FROM users WHERE user_id =42;
3
4-- BEST PRACTICE:
5SELECT user_id, email, first_name FROM users WHERE user_id =42;
Anti-Pattern 5: The OR Operator Across Unrelated Columns
A single query with WHERE col_a = 1 OR col_b = 2 frequently prevents index usage, resulting in an expensive index_merge or full table scan.
1-- ANTI-PATTERN:
2SELECT*FROM users WHERE email ='a@b.com'OR phone ='12345';
3
4-- BEST PRACTICE (Combine two separate index seeks via UNION):
5SELECT*FROM users WHERE email ='a@b.com'
6UNION
7SELECT*FROM users WHERE phone ='12345';
Summary Checklist for Production Queries
Are date and time queries structured with range comparisons instead of YEAR(), MONTH(), or DATE()?
Are string literals properly quoted to prevent implicit casting?
Are only necessary columns projected instead of SELECT *?
Are multiple conditions joined with AND rather than disjoint OR where possible?
Interactive Quiz• Knowledge Check
Multiple Choice Questions
Question 1
What does the term "SARGable" stand for in SQL query optimization?
Question 2
Why does WHERE DATE(created_at) = '2026-08-01' cause a full table scan on an indexed created_at column?
Question 3
If phone_number is VARCHAR, why does WHERE phone_number = 12345 perform poorly?
Question 4
Why is SELECT * considered an anti-pattern in production backend code?
Question 5
How can a slow WHERE col_a = 10 OR col_b = 20 query often be refactored to utilize two distinct single-column indexes?