Using functions in WHERE on indexed columns: WHERE YEAR(order_date) = 2024

Use range comparisons: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

Functions on indexed columns prevent index usage. Problem: WHERE YEAR(order_date) = 2024 (sequential scan, 1000ms for 100K rows). Solution: Range comparison. Example: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' (index scan, 1ms, 1000x faster). Why: Index stores original values (2024-01-15), not computed values (2024). Function must compute for every row (slow). Range comparison uses index directly (fast). Real-world: Amazon uses range comparisons for date filters (fast). Lesson: Avoid functions in WHERE on indexed columns.

Avoid functions in WHERE on indexed columns. Use range comparisons instead. Functions in SELECT are fine (applied after filtering).

Critical

Function prevents index usage, sequential scan (1000x slower)

Case-insensitive search with function: WHERE UPPER(email) = 'JOHN@EXAMPLE.COM'

Use generated column: ADD COLUMN email_upper AS (UPPER(email)) STORED, CREATE INDEX, WHERE email_upper = 'JOHN@EXAMPLE.COM'

UPPER() in WHERE prevents index usage. Problem: WHERE UPPER(email) = 'JOHN@EXAMPLE.COM' (sequential scan, 1000ms for 100K rows). Solution: Generated column + index. Example: ADD COLUMN email_upper AS (UPPER(email)) STORED, CREATE INDEX idx_email_upper ON users(email_upper), WHERE email_upper = 'JOHN@EXAMPLE.COM' (index scan, 1ms, 1000x faster). Benefits: Pre-computed (no runtime overhead), indexable (fast queries), automatic updates (when email changes). Real-world: Stripe uses generated columns for case-insensitive email search. Lesson: Use generated columns for frequently queried functions.

Use generated columns for case-insensitive search. Pre-computed, indexable, automatic updates. 1000x faster than function in WHERE.

High

Function prevents index usage, sequential scan (slow)

Not rounding currency values: SELECT price * quantity AS total

Use ROUND(): SELECT ROUND(price * quantity, 2) AS total

Floating point arithmetic has precision errors. Problem: 19.99 * 3 = 59.97000000000001 (precision error). Solution: ROUND(price * quantity, 2) = 59.97 (correct). Example: Order total without rounding: $59.97000000000001 (bad UX). With rounding: $59.97 (correct). Best practice: Always round currency to 2 decimal places. Alternative: Use DECIMAL(10, 2) type (exact precision). Real-world: Stripe uses ROUND(amount, 2) for all payment calculations. Lesson: Always round currency values.

Always round currency to 2 decimal places: ROUND(amount, 2). Or use DECIMAL(10, 2) type for exact precision.

Medium

Floating point precision errors, incorrect totals

Using IFNULL() for multiple NULL checks: IFNULL(IFNULL(phone, email), 'N/A')

Use COALESCE(): COALESCE(phone, email, 'N/A')

IFNULL() only accepts 2 arguments. Problem: Multiple NULL checks require nesting: IFNULL(IFNULL(phone, email), 'N/A') (verbose, hard to read). Solution: COALESCE() accepts multiple arguments: COALESCE(phone, email, 'N/A') (clean, readable). How it works: Returns first non-NULL value. Example: phone=NULL, email='john@example.com' returns 'john@example.com'. phone=NULL, email=NULL returns 'N/A'. Real-world: Shopify uses COALESCE() for contact information (phone, email, address). Lesson: Use COALESCE() for multiple NULL checks.

Use COALESCE() for multiple NULL checks. Returns first non-NULL value. Cleaner than nested IFNULL().

Low

Nested IFNULL() is verbose, hard to read