Column Order Importance

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

-- Index: (department_id, hire_date, employee_id)
 
-- Query 1: All equality predicates
SELECT * FROM employees
WHERE department_id = 50
  AND hire_date = '2023-01-15'
  AND employee_id = 1001;
-- Result: Uses all three columns efficiently ✅
 
-- Query 2: Equality, then range
SELECT * FROM employees
WHERE department_id = 50
  AND hire_date BETWEEN '2023-01-01' AND '2023-06-30'
  AND employee_id = 1001;
-- Result: Uses department_id and hire_date only ⚠️
-- The employee_id predicate cannot use the index range scan
-- (It may still filter, but doesn't narrow the scan)
 
-- Query 3: Range on first column
SELECT * FROM employees
WHERE department_id BETWEEN 40 AND 60
  AND hire_date = '2023-01-15'
  AND employee_id = 1001;
-- Result: Uses department_id only for range scan ⚠️
-- hire_date and employee_id cannot leverage index ordering
 
-- Why? Because within the range [40, 60], hire_dates are NOT contiguous:
-- (40, 2020-01-01), (40, 2023-01-15), (41, 2021-06-01), (42, 2023-01-15), ...
-- There's no single "2023-01-15 range" to scan

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

-- Scenario 1: E-commerce order lookup
-- Common query: Find orders for a customer in a date range
-- Pattern: customer_id = ? AND order_date BETWEEN ? AND ?
 
-- CORRECT ordering: Equality first, range second
CREATE INDEX idx_orders_optimal ON orders (customer_id, order_date);
 
-- INCORRECT ordering: Range column first
CREATE INDEX idx_orders_suboptimal ON orders (order_date, customer_id);
-- This would require scanning all dates to find the customer
 
 
-- Scenario 2: Employee filtering with multiple equalities
-- Common query: Find employees in a department with a specific job title
-- Pattern: department_id = ? AND job_title = ?
 
-- If department_id has higher selectivity (100 depts, 20 job titles):
CREATE INDEX idx_emp_v1 ON employees (department_id, job_title);
 
-- If job_title has higher selectivity (100 depts, 500 job titles):
CREATE INDEX idx_emp_v2 ON employees (job_title, department_id);
 
-- The higher-selectivity column first reduces rows examined
 
 
-- Scenario 3: Multi-predicate with ORDER BY
-- Common query: Recent orders for a customer
-- Pattern: customer_id = ? ORDER BY order_date DESC LIMIT 10
 
-- Optimal: Filter column + sort column
CREATE INDEX idx_orders_sorted ON orders (customer_id, order_date DESC);
-- Enables index scan in order, no filesort needed
 
 
-- Scenario 4: Covering index design
-- Common query: Customer order summary
-- Pattern: SELECT order_id, total, status 
--          WHERE customer_id = ? AND order_date BETWEEN ? AND ?
 
-- Covering index includes all needed columns
CREATE INDEX idx_orders_covering ON orders (
    customer_id,      -- Equality filter
    order_date,       -- Range filter
    order_id,         -- Output column
    total,            -- Output column
    status            -- Output column
);
-- Enables index-only scan

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

-- MySQL: Analyze column cardinality
SELECT 
    COLUMN_NAME,
    CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'orders'
ORDER BY CARDINALITY DESC;
 
-- PostgreSQL: Detailed statistics
SELECT 
    attname AS column_name,
    n_distinct,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE tablename = 'orders';
 
-- Calculate selectivity
-- For orders table (1,000,000 rows):
-- customer_id: 50,000 distinct → selectivity = 0.00002 (high)
-- status: 5 distinct → selectivity = 0.2 (low)
-- order_date: 1,000 distinct → selectivity = 0.001 (moderate)
 
-- Common query: customer_id = ? AND status = ? AND order_date BETWEEN ? AND ?
-- Optimal ordering:
-- 1. customer_id (equality, highest selectivity)
-- 2. status (equality, lower selectivity)
-- 3. order_date (range)
CREATE INDEX idx_orders_optimal ON orders (customer_id, status, order_date);

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

-- Scenario: Dashboard showing recent orders by category
-- Required: ORDER BY category_id ASC, order_date DESC
 
-- Standard index (both ASC) - CANNOT satisfy mixed order
CREATE INDEX idx_orders_v1 ON orders (category_id ASC, order_date ASC);
-- Result: Filesort required for DESC on order_date
 
-- Mixed sort order index - CAN satisfy the query
CREATE INDEX idx_orders_v2 ON orders (category_id ASC, order_date DESC);
-- Result: Index scan in natural order, no filesort
 
-- Query execution comparison:
 
-- With idx_orders_v1:
EXPLAIN SELECT * FROM orders
WHERE category_id = 5
ORDER BY category_id ASC, order_date DESC;
-- Extra: Using filesort (expensive for large results)
 
-- With idx_orders_v2:
EXPLAIN SELECT * FROM orders  
WHERE category_id = 5
ORDER BY category_id ASC, order_date DESC;
-- Extra: Using index (no filesort)
 
 
-- Modern databases support mixed sort orders:
-- MySQL 8.0+, PostgreSQL, SQL Server, Oracle all support this
CREATE INDEX idx_products ON products (
    category_id ASC,
    price DESC,
    product_name ASC
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- MISTAKE 1: Range before equality
-- Query: WHERE status = 'active' AND created_at > '2023-01-01'
CREATE INDEX idx_bad ON items (created_at, status);  -- ❌ Wrong order
CREATE INDEX idx_good ON items (status, created_at); -- ✅ Correct
 
-- MISTAKE 2: Leading column not used
-- Query: WHERE department_id = 50
CREATE INDEX idx_unused ON employees (manager_id, department_id);
-- Index is useless for this query! manager_id is leading.
 
-- MISTAKE 3: Wrong sort direction
-- Query: ORDER BY last_name ASC, hire_date DESC
CREATE INDEX idx_wrong_sort ON employees (last_name ASC, hire_date ASC);
-- Requires filesort because of mixed directions
 
CREATE INDEX idx_right_sort ON employees (last_name ASC, hire_date DESC);
-- No filesort needed
 
-- MISTAKE 4: Duplicate coverage
-- These indexes are redundant:
CREATE INDEX idx1 ON orders (customer_id);
CREATE INDEX idx2 ON orders (customer_id, order_date);
-- idx2 can serve all queries that idx1 serves!
-- Solution: Keep only idx2