SQL Function Overuse #

-- Query with a function in WHERE
EXPLAIN SELECT id, name FROM users WHERE LOWER(email) = '[email protected]';

-- +------+------+------+------+--------+-------------+
-- | type | key  | ref  | rows | Extra               |
-- +------+------+------+------+--------+-------------+
-- | ALL  | NULL | NULL | 980000 | Using where      |
-- +------+------+------+------+--------+-------------+
-- type = ALL     → full table scan
-- key  = NULL    → index not used
-- rows = 980000  → almost every row read

-- Query without a function in WHERE
EXPLAIN SELECT id, name FROM users WHERE email = '[email protected]';

-- +-------+-----------------+-------+------+-------+
-- | type  | key             | ref   | rows | Extra |
-- +-------+-----------------+-------+------+-------+
-- | ref   | idx_users_email | const |    1 | NULL  |
-- +-------+-----------------+-------+------+-------+
-- type = ref   → index used
-- rows = 1     → straight to the right row

-- ANTI-PATTERN: functions on WHERE columns
SELECT id, name FROM users
WHERE LOWER(email) = LOWER(?);
-- → Full scan, calling LOWER() millions of times per query

-- CORRECT — Option A: normalize data on insert/update
-- Always store emails in lowercase in the database
INSERT INTO users (name, email) VALUES ('Budi', LOWER('[email protected]'));
-- Now a simple query uses the index directly:
SELECT id, name FROM users WHERE email = ?;
-- Lowercase the input in the application layer before querying

-- CORRECT — Option B: use a case-insensitive collation
-- Columns with the _ci collation are case-insensitive by default
CREATE TABLE users (
    email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);
-- A query without LOWER() still finds '[email protected]' and '[email protected]'
SELECT id, name FROM users WHERE email = ?;
-- The index is still used because the column is compared directly, not via a function

-- ANTI-PATTERN: DATE() wrapping a DATETIME column
SELECT id, total FROM orders
WHERE DATE(created_at) = '2026-01-15';
-- → The index on created_at can't be used
-- → Full scan + DATE() called on every row

-- CORRECT: use a range query without functions on the column
SELECT id, total FROM orders
WHERE created_at >= '2026-01-15 00:00:00'
  AND created_at <  '2026-01-16 00:00:00';
-- → type: range, index used, only rows in the range are read

-- Or in Go, compute the range in the application layer:
start := time.Date(2026, 1, 15, 0, 0, 0, 0, time.UTC)
end   := start.AddDate(0, 0, 1)
db.Query("SELECT id, total FROM orders WHERE created_at >= ? AND created_at < ?",
    start, end)

-- ANTI-PATTERN: YEAR() and MONTH() functions
SELECT id, amount FROM transactions
WHERE YEAR(transaction_date) = 2026
  AND MONTH(transaction_date) = 1;
-- → Two functions in WHERE, index completely dead

-- CORRECT: convert to a range without functions on the column
SELECT id, amount FROM transactions
WHERE transaction_date >= '2026-01-01'
  AND transaction_date <  '2026-02-01';

-- For "this year" queries:
WHERE transaction_date >= '2026-01-01'
  AND transaction_date <  '2027-01-01';

-- For "last month" queries — compute in the application layer, not SQL:
-- firstDayLastMonth = the 1st of last month
-- firstDayThisMonth = the 1st of this month
WHERE transaction_date >= ? AND transaction_date < ?

-- ANTI-PATTERN: CAST on a WHERE column because the data types don't match
SELECT * FROM orders
WHERE CAST(user_id AS CHAR) = '12345';
-- → The index on user_id (INT) is unused because it's converted to CHAR

-- CORRECT — Option A: cast the input value, not the column
SELECT * FROM orders
WHERE user_id = CAST('12345' AS UNSIGNED);
-- → Or better: cast in the application layer, send an integer to the query
SELECT * FROM orders WHERE user_id = ?;  -- bind an integer parameter

-- CORRECT — Option B: fix the data type in the schema
-- If you're forced to CAST because the column type is wrong, that's a signal the schema must be fixed
-- See the Type on Join article for type migration strategies

-- ANTI-PATTERN: SUBSTRING() or LEFT() on WHERE columns
SELECT * FROM products
WHERE LEFT(sku, 3) = 'INV';
-- → Full scan, LEFT() called on every row

-- ANTI-PATTERN: LIKE with a leading wildcard
SELECT * FROM products
WHERE sku LIKE '%INV%';
-- → Also a full scan — a leading wildcard prevents index use

-- CORRECT: LIKE with a trailing-only wildcard can use an index
SELECT * FROM products
WHERE sku LIKE 'INV%';
-- → type: range, the sku index used for a prefix scan
-- → The database jumps directly to the index section starting with 'INV'

-- For mid-string searches (LIKE '%word%'):
-- Use Full-Text Search, not LIKE
-- See the Full-Text Index article for the correct strategy

-- ANTI-PATTERN: TRIM() on a column to cope with dirty data
SELECT * FROM customers
WHERE TRIM(phone_number) = '08123456789';
-- → Full scan because TRIM() wraps the column

-- Root cause: dirty data because it wasn't normalized on insert

-- CORRECT — Option A: normalize data on insert and update
INSERT INTO customers (phone_number) VALUES (TRIM(?));
UPDATE customers SET phone_number = TRIM(phone_number)
WHERE phone_number != TRIM(phone_number);
-- After the data is clean, query directly:
SELECT * FROM customers WHERE phone_number = ?;

-- CORRECT — Option B: normalize in the application layer
phone := strings.TrimSpace(input)
db.Query("SELECT * FROM customers WHERE phone_number = ?", phone)

-- DON'T: let dirty data into the database and cope with it at the query level

-- ANTI-PATTERN: COALESCE() on a WHERE column
SELECT * FROM products
WHERE COALESCE(category_id, 0) = 5;
-- → Full scan because COALESCE() wraps the column

-- CORRECT: separate NULL and non-NULL conditions
SELECT * FROM products
WHERE category_id = 5;
-- Or if NULL handling is really needed:
SELECT * FROM products
WHERE (category_id = 5 OR category_id IS NULL);
-- → Both conditions can use the index on category_id

-- For default values, it's better in the schema:
ALTER TABLE products
MODIFY COLUMN category_id INT UNSIGNED NOT NULL DEFAULT 0;
-- Now COALESCE is no longer needed

-- ANTI-PATTERN: functions in JOIN conditions
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON LOWER(o.customer_email) = LOWER(u.email);
-- → The index on o.customer_email unused
-- → The index on u.email unused
-- → For every orders row, scan all users while computing LOWER()
-- → Complexity approaching O(n × m): millions × millions of operations

-- CORRECT — Option A: use an integer foreign key, not email, for JOINs
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;
-- → eq_ref: primary key indexes on both sides, very efficient

-- CORRECT — Option B: if string JOINs can't be avoided,
-- normalize the join column data into a consistent format
-- Always store lowercase, use the same collation
-- Then JOIN without functions:
ON o.customer_email = u.email
-- (both already lowercase, same collation)

-- PostgreSQL: create a functional index for LOWER(email)
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Now a query with LOWER() in WHERE can use this index
EXPLAIN SELECT id, name FROM users WHERE LOWER(email) = '[email protected]';
-- → Index Scan using idx_users_email_lower (index used)

-- MySQL 8.0+: functional index
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));
-- Note the double parentheses — MySQL's syntax for functional indexes

-- An example for DATE():
-- PostgreSQL
CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));
-- Now WHERE DATE(created_at) = '2026-01-15' can use this index

-- Normalization strategy: apply at the insert/update level, not the query level

-- Email: always lowercase
INSERT INTO users (email) VALUES (LOWER(TRIM(?)));

-- Phone: remove non-digit characters
-- (do this in the application layer before insert)
-- Go: regexp.MustCompile(`\D`).ReplaceAllString(phone, "")
-- Result: "0812-345 6789" → "08123456789"

-- Search strings: store a normalized version
-- in a separate column if needed (search_name from name)
ALTER TABLE products ADD COLUMN name_normalized VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(TRIM(name))) STORED;
CREATE INDEX idx_products_name_normalized ON products(name_normalized);

-- Now case-insensitive search uses the generated column:
SELECT * FROM products WHERE name_normalized = LOWER(TRIM(?));
-- → Index used, the function is only called once for the input
-- → Not millions of times for every row in the table

-- Enable the slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- queries > 500ms go to the log
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Analyze with mysqldumpslow
-- mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
-- → Shows the 20 slowest queries by total execution time

-- Find tables with large row counts to prioritize
SELECT
    TABLE_NAME,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_ROWS > 100000
ORDER BY TABLE_ROWS DESC;

-- After finding large tables, run EXPLAIN on the queries
-- touching those tables — prioritize ones with functions in WHERE

-- ✗ Anti-pattern 1: LOWER() on a WHERE column
WHERE LOWER(email) = '[email protected]'
-- ✓ Solution: store emails lowercase, or use a _ci collation

-- ✗ Anti-pattern 2: DATE() on a DATETIME column
WHERE DATE(created_at) = '2026-01-15'
-- ✓ Solution: WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'

-- ✗ Anti-pattern 3: YEAR() and MONTH()
WHERE YEAR(order_date) = 2026 AND MONTH(order_date) = 3
-- ✓ Solution: WHERE order_date >= '2026-03-01' AND order_date < '2026-04-01'

-- ✗ Anti-pattern 4: CAST() on a column to adjust the type
WHERE CAST(user_id AS CHAR) = '999'
-- ✓ Solution: cast in the application layer, fix the column type in the schema

-- ✗ Anti-pattern 5: LEFT() for prefix matching
WHERE LEFT(product_code, 3) = 'PRD'
-- ✓ Solution: WHERE product_code LIKE 'PRD%'

-- ✗ Anti-pattern 6: TRIM() to cope with dirty data
WHERE TRIM(phone) = '08123456789'
-- ✓ Solution: normalize data on insert, query directly

-- ✗ Anti-pattern 7: COALESCE() on filter columns
WHERE COALESCE(status, 'pending') = 'pending'
-- ✓ Solution: WHERE status = 'pending' OR status IS NULL

-- ✗ Anti-pattern 8: functions in JOIN conditions
ON LOWER(o.email) = LOWER(u.email)
-- ✓ Solution: use integer FKs, or normalize the join columns