Index Selectivity & Cardinality

-- Check index cardinality (higher = more selective)
SHOW INDEX FROM orders;
-- Columns: Key_name, Column_name, Cardinality
-- Cardinality is an estimate — refreshed by ANALYZE TABLE

-- Manually calculate selectivity for a column
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT customer_id) / COUNT(*) AS customer_selectivity,
    COUNT(DISTINCT id) / COUNT(*) AS id_selectivity
FROM orders;
-- status: 0.00001 (5 distinct values / 500k rows) → LOW selectivity
-- customer_id: 0.05 (25k customers / 500k rows) → MEDIUM selectivity
-- id: 1.0 (500k / 500k) → HIGH selectivity (primary key)

-- Low-selectivity index example: status has 5 values
-- Querying WHERE status = 'PLACED' returns 20% of rows
-- Optimiser may prefer full scan over index lookup

-- Refresh statistics after bulk load
ANALYZE TABLE orders;

-- information_schema cardinality
SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders'
ORDER BY SEQ_IN_INDEX;

-- EXPLAIN shows optimiser decision
EXPLAIN SELECT * FROM orders WHERE status = 'PLACED';
-- If 'status' index has low selectivity:
-- type: ALL (full scan preferred over index)
-- key: NULL
-- rows: 500000

-- Compare with high-selectivity column
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- type: ref (index lookup)
-- key: idx_customer_id
-- rows: 20

-- Force index to compare execution plans
EXPLAIN SELECT * FROM orders FORCE INDEX (idx_status)
WHERE status = 'PLACED';
-- rows: 100000 (higher than full scan estimate — confirms optimiser was right)

-- When to index low-selectivity columns:
-- 1. Combined with high-selectivity columns (composite index)
-- 2. The WHERE + ORDER BY pattern aligns with composite index
CREATE INDEX idx_status_created ON orders (status, created_at DESC);
-- Now: WHERE status = 'SHIPPED' ORDER BY created_at DESC
-- is efficient — status filters, created_at provides sort order

-- Optimal prefix length analysis for VARCHAR(500) column
SELECT
    COUNT(DISTINCT LEFT(description, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(description, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(description, 20)) / COUNT(*) AS sel_20,
    COUNT(DISTINCT description) / COUNT(*) AS sel_full
FROM products;
-- sel_5: 0.32, sel_10: 0.68, sel_20: 0.91, sel_full: 0.98
-- → prefix of 20 chars gives 93% of full column selectivity at much lower storage

-- Create prefix index
CREATE INDEX idx_description_prefix ON products (description(20));

-- Prefix indexes CANNOT be used for ORDER BY or covering index lookups
-- Only useful for = and LIKE 'prefix%' — NOT LIKE '%suffix%'

-- Composite index: status (low) + created_at (high cardinality date)
-- Leading column filters; second column provides ordering
EXPLAIN SELECT * FROM orders
WHERE status = 'SHIPPED' ORDER BY created_at DESC LIMIT 50;
-- With idx_status_created: type=ref, Extra="Using index condition"
-- Without: filesort on large result set