MySQL functions enable data transformation and calculations in SQL. Real examples: (1) String manipulation: E-commerce sites use CONCAT() for full names, SUBSTRING() for product codes. Amazon uses UPPER() for case-insensitive search. (2) Math calculations: Financial apps use ROUND() for currency, CEIL() for pagination. Stripe uses ROUND(amount, 2) for payments. (3) Date formatting: Analytics dashboards use DATE_FORMAT() for reports, DATE_ADD() for date ranges. Google Analytics uses DATE_FORMAT() for grouping. (4) Aggregations: Business intelligence uses COUNT(), SUM(), AVG() for metrics. Shopify uses SUM(total) for revenue. Trade-offs: Functions in WHERE prevent index usage (slow), use range comparisons instead. Functions in SELECT are fine (applied after filtering).

Amazon: String Functions for Product Search

Amazon needs case-insensitive product search across millions of products. Challenge: UPPER() in WHERE prevents index usage. Solution: Use generated columns with indexes.

Amazon uses generated columns for case-insensitive search: (1) name_upper generated column stores UPPER(name), (2) Index on name_upper for fast queries, (3) WHERE name_upper LIKE '%LAPTOP%' uses index. Architecture: products table with name_upper generated column, index on name_upper. Benefits: 1000x faster than function in WHERE, automatic updates when name changes.

ALTER TABLE products

ADD COLUMN name_upper VARCHAR(200) AS (UPPER(name)) STORED;

CREATE INDEX idx_name_upper ON products(name_upper);

SELECT * FROM products

WHERE name_upper LIKE '%LAPTOP%';

Millions of products, case-insensitive search

Generated columns: 1000x faster

Index on generated column

Automatic updates

Lesson: Use generated columns for functions

MySQL

Stripe: Math Functions for Payment Calculations

Stripe processes millions of payments with precise currency calculations. Challenge: Floating point precision errors. Solution: Use ROUND() for all currency calculations.

Stripe uses ROUND() for payment calculations: (1) ROUND(amount, 2) for all currency values, (2) DECIMAL(10, 2) type for exact precision, (3) Calculate in database, format in application. Architecture: payments table with amount DECIMAL(10, 2), ROUND() in all calculations. Benefits: No precision errors, exact currency values.

CREATE TABLE payments (

payment_id INT PRIMARY KEY,

amount DECIMAL(10, 2)

);

SELECT

ROUND(SUM(amount), 2) AS total_revenue

FROM payments;

Millions of payments, precise calculations

ROUND(amount, 2) for all currency

DECIMAL(10, 2) for exact precision

No floating point errors

Lesson: Always round currency

MySQL

Google Analytics: Date Functions for Time Series

Google Analytics groups billions of events by time period. Challenge: Efficient date grouping. Solution: Use DATE_FORMAT() for grouping, indexes on date columns.

Google Analytics uses DATE_FORMAT() for time series: (1) DATE_FORMAT(date, '%Y-%m') groups by month, (2) Index on date column for fast filtering, (3) Aggregate functions (COUNT, SUM) for metrics. Architecture: events table with date column, index on date, DATE_FORMAT() in GROUP BY. Benefits: Efficient grouping, fast queries with index.

SELECT

DATE_FORMAT(event_date, '%Y-%m') AS month,

COUNT(*) AS event_count

FROM events

WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)

GROUP BY DATE_FORMAT(event_date, '%Y-%m');