WHERE YEAR(order_date) = 2024

WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

Functions on indexed columns prevent index usage. The database must evaluate YEAR() for every row, making indexes useless. Use range comparisons instead to enable index seeks.

Avoid functions on indexed columns in WHERE clauses

Critical

Function prevents index usage

SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA')

SELECT o.* FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id WHERE c.country = 'USA'

IN with subqueries can perform poorly because the subquery result must be materialized. JOINs allow the optimizer to choose better execution strategies and use indexes more effectively.

Use JOINs instead of IN with subqueries for better performance

High

IN with subquery can be slow for large result sets

SELECT * FROM large_table ORDER BY created_at DESC

SELECT id, name, created_at FROM large_table ORDER BY created_at DESC LIMIT 100

Without LIMIT, the database must retrieve and sort all rows, even if you only need the first few. SELECT * also transfers unnecessary data. Always use LIMIT and select only needed columns.

Always use LIMIT when you don't need all rows

High

Retrieving and sorting all rows without LIMIT

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 5

CREATE INDEX idx_orders_customer ON orders(customer_id); SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 5

GROUP BY operations benefit significantly from indexes. Without an index, the database must sort all rows. An index on the GROUP BY column enables more efficient grouping strategies.

Index columns used in GROUP BY clauses

High

Missing index on GROUP BY column

SELECT * FROM orders WHERE status = 'pending' OR status = 'processing' OR status = 'shipped'

SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped')

Multiple OR conditions can prevent index usage and are harder to optimize. IN clauses are cleaner and allow the optimizer to use index range scans more effectively.

Use IN instead of multiple OR conditions on the same column

Medium

Multiple OR conditions instead of IN