Why do functions in WHERE clauses prevent index usage? How can you optimize queries with functions?

Functions in WHERE prevent index usage because indexes store original values, not computed values. How it works: Index stores original values (email='john@example.com'). Function computes new value (UPPER(email)='JOHN@EXAMPLE.COM'). Index cannot be used (must compute for every row). Example: WHERE YEAR(order_date) = 2024. Index stores dates (2024-01-15), not years (2024). Must compute YEAR() for every row (sequential scan, slow). Optimization strategies: (1) Range comparisons: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' (uses index, fast). (2) Generated columns: ADD COLUMN year AS (YEAR(order_date)) STORED, CREATE INDEX, WHERE year = 2024 (uses index, fast). (3) Move to SELECT: WHERE order_date >= '2024-01-01', SELECT YEAR(order_date) (function after filtering, fine). Real-world: Amazon uses range comparisons for date filters (fast). Stripe uses generated columns for UPPER(email) (fast). Lesson: Avoid functions in WHERE on indexed columns.

Explain the difference between IFNULL() and COALESCE(). When would you use each?

IFNULL() and COALESCE() both handle NULL values but differ in arguments. IFNULL(val, default): Takes 2 arguments, returns default if val is NULL. Example: IFNULL(phone, 'N/A') returns phone or 'N/A'. COALESCE(val1, val2, ...): Takes multiple arguments, returns first non-NULL. Example: COALESCE(phone, email, 'N/A') returns phone if not NULL, else email, else 'N/A'. Use IFNULL() when: (1) Only 2 values (value and default), (2) Simple NULL replacement. Use COALESCE() when: (1) Multiple values (phone, email, address), (2) Need first non-NULL from list. Performance: Both are equivalent for 2 arguments. COALESCE() is standard SQL (portable). IFNULL() is MySQL-specific. Real-world: Shopify uses COALESCE() for contact info (phone, email, address). Stripe uses IFNULL() for simple defaults. Lesson: Use COALESCE() for multiple values, IFNULL() for simple cases.

How do you format currency values in MySQL? What are the best practices?

Currency formatting in MySQL uses ROUND() and CONCAT(). Best practices: (1) Always round to 2 decimals: ROUND(amount, 2) prevents floating point errors. (2) Format for display: CONCAT('$', FORMAT(amount, 2)) adds $ and commas. (3) Use DECIMAL type: DECIMAL(10, 2) for exact precision (no floating point errors). (4) Calculate in database: SUM(price * quantity) then round. (5) Format in application: Easier to change, supports internationalization. Example: SELECT CONCAT('$', FORMAT(ROUND(price * quantity, 2), 2)) AS total FROM orders. Returns: $1,234.56. Alternative: Calculate in database, format in application. Real-world: Stripe calculates in database (ROUND(amount, 2)), formats in application (currency symbol, locale). Lesson: Always round currency, use DECIMAL type, format in application when possible.

Write queries to: (1) Find orders from last 30 days with revenue by day, (2) Format product names as slugs (lowercase, spaces to hyphens), (3) Calculate pagination (20 items per page).

-- (1) Orders from last 30 days with revenue by day

SELECT

DATE_FORMAT(order_date, '%Y-%m-%d') AS date,

DATE_FORMAT(order_date, '%W, %M %d') AS day_name,

COUNT(*) AS order_count,

CONCAT('$', FORMAT(SUM(total), 2)) AS revenue,

CONCAT('$', FORMAT(AVG(total), 2)) AS avg_order

FROM orders

WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

GROUP BY DATE_FORMAT(order_date, '%Y-%m-%d')

ORDER BY date DESC;

-- (2) Format product names as slugs

SELECT

product_id,

name,

LOWER(REPLACE(name, ' ', '-')) AS slug

FROM products;

-- 'MacBook Pro' -> 'macbook-pro'

-- Better: Handle special characters

SELECT

product_id,

name,

LOWER(

REPLACE(

REPLACE(

REPLACE(name, ' ', '-'),

'&', 'and'

),

',', ''

)

) AS slug

FROM products;

-- 'MacBook Pro, 16"' -> 'macbook-pro-16'