Functions on Indexed Columns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Table with indexed column
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    hire_date DATE NOT NULL
);
 
CREATE INDEX idx_employees_email ON employees(email);
CREATE INDEX idx_employees_hire_date ON employees(hire_date);
 
-- SEARCHABLE (SARGable) - Uses index seek
SELECT * FROM employees WHERE email = 'john.doe@company.com';
-- Database navigates index directly to 'john.doe@company.com'
 
-- NON-SEARCHABLE (Non-SARGable) - Full scan despite index
SELECT * FROM employees WHERE UPPER(email) = 'JOHN.DOE@COMPANY.COM';
-- Database must:
-- 1. Read every row in the index/table
-- 2. Apply UPPER() to each email value
-- 3. Compare result to 'JOHN.DOE@COMPANY.COM'
-- The index exists but provides NO navigational benefit
 
-- Another non-SARGable example
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- Must evaluate YEAR() on every hire_date value
-- Cannot seek to "2023" in the hire_date index
 
-- The fundamental rule:
-- function(column) = value    ← NON-SARGable, full scan
-- column = value              ← SARGable, index seek
-- column = function(value)    ← SARGable, index seek (function on the right is OK)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- PROBLEM: Case-insensitive search using function
SELECT * FROM customers WHERE UPPER(email) = UPPER('John@Example.com');
SELECT * FROM products WHERE LOWER(name) LIKE LOWER('wireless%');
 
-- Why this happens:
-- Developers want case-insensitive matching
-- Default collations are often case-sensitive
-- UPPER/LOWER seems like an obvious solution
 
-- The performance cost:
-- 1 million customers → 1 million UPPER() calls
-- Index completely ignored
 
-- SOLUTIONS:
 
-- Solution 1: Use case-insensitive collation at column level
ALTER TABLE customers 
ALTER COLUMN email VARCHAR(255) COLLATE Latin1_General_CI_AS;
-- CI = Case Insensitive
-- Now: WHERE email = 'John@Example.com' works case-insensitively
 
-- Solution 2: Use case-insensitive collation in query (SQL Server)
SELECT * FROM customers 
WHERE email COLLATE Latin1_General_CI_AS = 'John@Example.com';
 
-- Solution 3: PostgreSQL ILIKE for case-insensitive LIKE
SELECT * FROM products WHERE name ILIKE 'wireless%';
-- Still may not use index efficiently; consider citext extension
 
-- Solution 4: Store normalized version in separate indexed column
ALTER TABLE customers ADD email_lower VARCHAR(255);
UPDATE customers SET email_lower = LOWER(email);
CREATE INDEX idx_customers_email_lower ON customers(email_lower);
-- Query: WHERE email_lower = LOWER('John@Example.com')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- PROBLEMS: Extracting date components
 
-- Finding all orders from 2023
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
 
-- Finding all events in March
SELECT * FROM events WHERE MONTH(event_date) = 3;
 
-- Finding Monday events
SELECT * FROM events WHERE DATEPART(dw, event_date) = 2;
 
-- All of these apply functions to the indexed column
-- All trigger full table scans
 
-- SOLUTIONS: Rewrite as range predicates
 
-- Solution for YEAR() - convert to range
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' 
  AND order_date < '2024-01-01';
-- Index on order_date can seek to start of 2023, scan until 2024
 
-- Solution for MONTH() - also range (but may span years)
SELECT * FROM events
WHERE (order_date >= '2023-03-01' AND order_date < '2023-04-01')
   OR (order_date >= '2024-03-01' AND order_date < '2024-04-01');
-- Multiple ranges if spanning multiple years
 
-- Alternative: Computed column for month (if frequent query pattern)
ALTER TABLE events ADD event_month AS MONTH(event_date) PERSISTED;
CREATE INDEX idx_events_month ON events(event_month);
-- Now: WHERE event_month = 3 uses the index
 
-- Solution for day of week - harder, may need computed column
ALTER TABLE events ADD event_dow AS DATEPART(dw, event_date) PERSISTED;
CREATE INDEX idx_events_dow ON events(event_dow);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- PROBLEMS: String manipulation
 
-- Finding by area code (first 3 chars of phone)
SELECT * FROM customers WHERE LEFT(phone, 3) = '415';
SELECT * FROM customers WHERE SUBSTRING(phone, 1, 3) = '415';
 
-- Finding SKUs in a category (prefix match)
SELECT * FROM products WHERE LEFT(sku, 2) = 'EL';
 
-- Trimming whitespace
SELECT * FROM items WHERE TRIM(code) = 'ABC123';
 
-- SOLUTIONS:
 
-- For prefix matching: Use LIKE with trailing wildcard
SELECT * FROM customers WHERE phone LIKE '415%';
-- B-tree indexes support prefix matching (leading wildcard is the problem)
 
SELECT * FROM products WHERE sku LIKE 'EL%';
-- Index seek to 'EL', scan until values no longer start with 'EL'
 
-- For trim issues: Clean data on insert, not on query
-- Add CHECK constraint and trigger to ensure no leading/trailing whitespace
ALTER TABLE items ADD CONSTRAINT chk_code_trimmed 
CHECK (code = TRIM(code));
 
-- If legacy data has whitespace, clean it once:
UPDATE items SET code = TRIM(code) WHERE code != TRIM(code);
 
-- Alternatively, create computed column
ALTER TABLE items ADD code_trimmed AS TRIM(code) PERSISTED;
CREATE INDEX idx_items_code_trimmed ON items(code_trimmed);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- PROBLEMS: Arithmetic on columns
 
-- Finding products with 20% margin
SELECT * FROM products WHERE price - cost > price * 0.20;
-- Operations on both price and cost columns
 
-- Finding transactions over a threshold after fee
SELECT * FROM transactions WHERE amount - 2.50 > 100;
 
-- Dividing to find rate
SELECT * FROM metrics WHERE total / count > 50;
 
-- SOLUTIONS: Algebraic manipulation to isolate column
 
-- Margin calculation: Rearrange inequality
-- price - cost > price * 0.20
-- price - cost > 0.2 * price
-- price - 0.2 * price > cost
-- 0.8 * price > cost
-- price > cost / 0.8
-- price > cost * 1.25
 
SELECT * FROM products WHERE price > cost * 1.25;
-- Still has operation on cost column...
-- If you can't avoid operations, consider computed column
 
-- Transaction threshold: Move constant to right side
-- amount - 2.50 > 100
-- amount > 102.50
SELECT * FROM transactions WHERE amount > 102.50;
-- Now index on amount can be used!
 
-- Rate calculation: Multiply both sides
-- total / count > 50
-- total > 50 * count
SELECT * FROM metrics WHERE total > 50 * count;
-- Operation still on count... 
-- For complex cases, computed columns may be necessary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

-- ========================================
-- YEAR() extraction
-- ========================================
 
-- Non-SARGable
SELECT * FROM orders WHERE YEAR(order_date) = 2023;
 
-- SARGable
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' 
  AND order_date < '2024-01-01';
 
-- ========================================
-- MONTH() extraction (requires year context)
-- ========================================
 
-- Non-SARGable
SELECT * FROM orders WHERE MONTH(order_date) = 12;
 
-- SARGable (assuming specific year or range of years)
SELECT * FROM orders 
WHERE order_date >= '2023-12-01' AND order_date < '2024-01-01';
 
-- For all Decembers across multiple years:
SELECT * FROM orders 
WHERE (order_date >= '2022-12-01' AND order_date < '2023-01-01')
   OR (order_date >= '2023-12-01' AND order_date < '2024-01-01');
-- Consider partitioning or computed column if this is common
 
-- ========================================
-- DAY() extraction
-- ========================================
 
-- Non-SARGable (find all orders on 15th of any month)
SELECT * FROM orders WHERE DAY(order_date) = 15;
 
-- This is hard to rewrite efficiently
-- Options:
-- 1. Computed persisted column: ALTER TABLE ADD order_day AS DAY(order_date)
-- 2. Accept full scan for rare queries
-- 3. Partition by day if pattern is critical
 
-- ========================================
-- DATE() or CAST to DATE from DATETIME
-- ========================================
 
-- Non-SARGable
SELECT * FROM events WHERE DATE(event_time) = '2023-06-15';
SELECT * FROM events WHERE CAST(event_time AS DATE) = '2023-06-15';
 
-- SARGable
SELECT * FROM events 
WHERE event_time >= '2023-06-15 00:00:00'
  AND event_time < '2023-06-16 00:00:00';
 
-- Or more precisely:
SELECT * FROM events 
WHERE event_time >= '2023-06-15'
  AND event_time < DATEADD(day, 1, '2023-06-15');
 
-- ========================================
-- DATEDIFF() for "recent" records
-- ========================================
 
-- Non-SARGable (find orders in last 30 days)
SELECT * FROM orders 
WHERE DATEDIFF(day, order_date, GETDATE()) <= 30;
 
-- SARGable (compute the boundary date once)
SELECT * FROM orders 
WHERE order_date >= DATEADD(day, -30, GETDATE());
-- DATEADD is computed once; order_date is naked
 
-- ========================================
-- Time component extraction
-- ========================================
 
-- Non-SARGable (find events at noon)
SELECT * FROM events WHERE DATEPART(hour, event_time) = 12;
 
-- SARGable requires range within each day
-- Complex if spanning multiple days; consider computed column
 
-- ========================================
-- Date arithmetic
-- ========================================
 
-- Non-SARGable
SELECT * FROM subscriptions WHERE end_date + 30 >= GETDATE();
 
-- SARGable (move constant to right side)
SELECT * FROM subscriptions WHERE end_date >= DATEADD(day, -30, GETDATE());

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- SQL Server: Persisted Computed Column
-- The PERSISTED keyword stores the computed value physically
ALTER TABLE employees
ADD hire_year AS YEAR(hire_date) PERSISTED;
 
CREATE INDEX idx_employees_hire_year ON employees(hire_year);
 
-- Now this query uses the index:
SELECT * FROM employees WHERE hire_year = 2023;
-- Translates to seeking on idx_employees_hire_year
 
-- PostgreSQL: Generated Column (stored)
ALTER TABLE employees
ADD COLUMN hire_year INT GENERATED ALWAYS AS (EXTRACT(YEAR FROM hire_date)) STORED;
 
CREATE INDEX idx_employees_hire_year ON employees(hire_year);
 
-- MySQL 5.7+: Generated Column
ALTER TABLE employees
ADD COLUMN hire_year INT AS (YEAR(hire_date)) STORED;
 
CREATE INDEX idx_employees_hire_year ON employees(hire_year);
 
-- ========================================
-- Common computed column patterns
-- ========================================
 
-- Case-insensitive search column
ALTER TABLE customers
ADD email_lower AS LOWER(email) PERSISTED;
CREATE INDEX idx_customers_email_lower ON customers(email_lower);
-- Query: WHERE email_lower = LOWER(@input)
 
-- Area code extraction
ALTER TABLE contacts
ADD phone_area_code AS LEFT(phone, 3) PERSISTED;
CREATE INDEX idx_contacts_area_code ON contacts(phone_area_code);
-- Query: WHERE phone_area_code = '415'
 
-- Full name for searching
ALTER TABLE users
ADD full_name AS CONCAT(first_name, ' ', last_name) PERSISTED;
CREATE INDEX idx_users_full_name ON users(full_name);
-- Query: WHERE full_name LIKE 'John Doe%'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

-- PostgreSQL: Expression Index
CREATE INDEX idx_customers_email_lower 
ON customers(LOWER(email));
 
-- The optimizer automatically uses this index when query matches:
SELECT * FROM customers WHERE LOWER(email) = 'john@example.com';
-- No query rewrite needed; index directly supports the expression
 
-- More PostgreSQL expression index examples
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM order_date));
CREATE INDEX idx_products_name_upper ON products(UPPER(name));
CREATE INDEX idx_events_date ON events(DATE(event_time));
 
-- Oracle: Function-Based Index
CREATE INDEX idx_customers_email_lower 
ON customers(LOWER(email));
 
CREATE INDEX idx_employees_hire_year 
ON employees(EXTRACT(YEAR FROM hire_date));
 
-- The function in the index MUST match the function in the query exactly
-- LOWER(email) index won't help with UPPER(email) query
 
-- MySQL 8.0+: Functional Index (as of 8.0.13)
CREATE INDEX idx_customers_email_lower 
ON customers((LOWER(email)));
-- Note the extra parentheses around the expression
 
CREATE INDEX idx_orders_year 
ON orders((YEAR(order_date)));
 
-- ========================================
-- Virtual vs Persisted/Stored
-- ========================================
 
-- Virtual computed columns (SQL Server, non-persisted):
ALTER TABLE employees ADD hire_year AS YEAR(hire_date);
-- Not stored; computed on each access
-- CAN be indexed, but index stores the value anyway
 
-- Expression indexes don't store a column, just the index:
-- Storage: Only index structure, not an extra column
-- Downside: Can't SELECT the computed value directly

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

-- ========================================
-- SQL Server Collation Solutions
-- ========================================
 
-- Check current column collation
SELECT 
    COLUMN_NAME, 
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'customers' AND COLUMN_NAME = 'email';
 
-- Change column to case-insensitive collation
ALTER TABLE customers
ALTER COLUMN email VARCHAR(255) COLLATE Latin1_General_CI_AS NOT NULL;
 
-- Now these queries are equivalent (and both use index):
SELECT * FROM customers WHERE email = 'John@Example.com';
SELECT * FROM customers WHERE email = 'john@example.com';
SELECT * FROM customers WHERE email = 'JOHN@EXAMPLE.COM';
-- All find the same rows; index is used
 
-- Query-level collation override (if you can't change column)
SELECT * FROM customers 
WHERE email COLLATE Latin1_General_CI_AS = 'John@Example.com';
-- Warning: This may still not use index efficiently
-- Column-level collation is better
 
-- ========================================
-- PostgreSQL Case-Insensitivity
-- ========================================
 
-- Option 1: Use ILIKE for case-insensitive pattern matching
SELECT * FROM products WHERE name ILIKE 'widget%';
-- ILIKE may not use index efficiently for B-tree indexes
 
-- Option 2: Use citext extension (case-insensitive text type)
CREATE EXTENSION IF NOT EXISTS citext;
 
ALTER TABLE customers ALTER COLUMN email TYPE citext;
-- Now equality comparisons are case-insensitive
 
SELECT * FROM customers WHERE email = 'John@Example.com';
-- Matches 'john@example.com' and uses index
 
-- Option 3: Expression index on LOWER()
CREATE INDEX idx_customers_email_lower ON customers(LOWER(email));
SELECT * FROM customers WHERE LOWER(email) = LOWER('John@Example.com');
-- Expression index handles this efficiently
 
-- ========================================
-- MySQL Case-Insensitivity
-- ========================================
 
-- MySQL default collations are often case-insensitive
-- Check current collation:
SHOW FULL COLUMNS FROM customers LIKE 'email';
 
-- Common case-insensitive collations:
-- utf8mb4_general_ci (case insensitive)
-- utf8mb4_unicode_ci (case insensitive, more accurate sorting)
 
-- Change column collation:
ALTER TABLE customers 
MODIFY email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
-- Binary collation for case-sensitive when needed:
ALTER TABLE codes
MODIFY code VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

// C# / Entity Framework Examples
 
// PROBLEM 1: String manipulation methods
// Developer writes:
var customers = context.Customers
    .Where(c => c.Email.ToLower() == email.ToLower())
    .ToList();
 
// EF generates:
// SELECT * FROM Customers WHERE LOWER(Email) = LOWER(@email)
// Function on column → full scan
 
// SOLUTION: Use case-insensitive collation or EF.Functions
var customers = context.Customers
    .Where(c => EF.Functions.Like(c.Email, email))  // Uses LIKE, respects collation
    .ToList();
 
// Or configure collation in model:
modelBuilder.Entity<Customer>()
    .Property(c => c.Email)
    .UseCollation("SQL_Latin1_General_CP1_CI_AS");
 
// ----------------------------------------
 
// PROBLEM 2: Date component properties
// Developer writes:
var orders = context.Orders
    .Where(o => o.OrderDate.Year == 2023)
    .ToList();
 
// EF generates:
// SELECT * FROM Orders WHERE DATEPART(year, OrderDate) = 2023
// Function on column → full scan
 
// SOLUTION: Use date range
DateTime startDate = new DateTime(2023, 1, 1);
DateTime endDate = new DateTime(2024, 1, 1);
var orders = context.Orders
    .Where(o => o.OrderDate >= startDate && o.OrderDate < endDate)
    .ToList();
 
// ----------------------------------------
 
// PROBLEM 3: String length comparisons
// Developer writes:
var longNames = context.Products
    .Where(p => p.Name.Length > 50)
    .ToList();
 
// EF generates:
// SELECT * FROM Products WHERE LEN(Name) > 50
// Function on column → full scan
 
// SOLUTION: Computed column in database
// ALTER TABLE Products ADD NameLength AS LEN(Name) PERSISTED;
// CREATE INDEX idx_name_length ON Products(NameLength);
// Then map to entity property

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

// Java / Hibernate / JPA Examples
 
// PROBLEM 1: JPQL with functions
// Developer writes:
String jpql = "SELECT c FROM Customer c WHERE LOWER(c.email) = LOWER(:email)";
Query query = em.createQuery(jpql);
// Generates: SELECT * FROM customers WHERE LOWER(email) = LOWER(?)
// Full scan
 
// SOLUTION: Use database-level case-insensitivity
// Or use native query with collation-aware comparison
 
// PROBLEM 2: Criteria API with functions
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> cq = cb.createQuery(Customer.class);
Root<Customer> root = cq.from(Customer.class);
 
// This applies function to column:
cq.where(cb.equal(cb.lower(root.get("email")), email.toLowerCase()));
// Generates: WHERE LOWER(email) = ?
 
// SOLUTION: Ensure data is normalized on insert
// Store email as lowercase, search with lowercase
cq.where(cb.equal(root.get("email"), email.toLowerCase()));
// Requires: email always stored as lowercase
 
// PROBLEM 3: Date criteria
// This is problematic:
cq.where(cb.equal(cb.function("YEAR", Integer.class, root.get("hireDate")), 2023));
 
// SOLUTION: Date range
Calendar start = new GregorianCalendar(2023, 0, 1);
Calendar end = new GregorianCalendar(2024, 0, 1);
cq.where(cb.and(
    cb.greaterThanOrEqualTo(root.get("hireDate"), start.getTime()),
    cb.lessThan(root.get("hireDate"), end.getTime())
));