Indexes — Cheat Sheet

Indexes Overview

-- PRIMARY KEY (clustered index): the table IS the index
-- Rows stored in PK order on disk; all secondary indexes include PK
CREATE TABLE orders (
    id         BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- clustered
    ...
);

-- Single-column secondary index: speeds up equality and range on one column
CREATE INDEX idx_status ON orders (status);

-- Composite (multi-column) index: leftmost prefix rule applies
-- Covers: status, status+customer_id, status+customer_id+created_at
CREATE INDEX idx_status_customer_date ON orders (status, customer_id, created_at);

-- UNIQUE index: enforces uniqueness + fast lookup (same as regular index + constraint)
CREATE UNIQUE INDEX idx_email ON users (email);

-- FULLTEXT index: relevance-based text search with MATCH() ... AGAINST()
CREATE FULLTEXT INDEX idx_description ON products (description);
SELECT * FROM products WHERE MATCH(description) AGAINST ('wireless headphones' IN BOOLEAN MODE);

-- Prefix index: index only first N characters (saves space for long VARCHAR)
CREATE INDEX idx_url_prefix ON pages (url(50));
-- Cannot be used for ORDER BY or GROUP BY (not covering)

B-Tree Index

-- Clustered index = PRIMARY KEY
-- Row data is physically ordered by customer_id
CREATE TABLE customers (
    customer_id INT        NOT NULL AUTO_INCREMENT,
    email       VARCHAR(255) NOT NULL,
    name        VARCHAR(100),
    city        VARCHAR(50),
    PRIMARY KEY (customer_id)          -- clustered index
);

-- Secondary index on email
CREATE INDEX idx_email ON customers (email);

-- When MySQL uses idx_email to find a row:
-- 1. Traverse idx_email B-Tree to find leaf with email value → gets customer_id
-- 2. Traverse PRIMARY KEY B-Tree with that customer_id → gets full row
-- This is the "bookmark lookup" — 2 B-Tree traversals

-- Avoid it with a covering index (include all needed columns):
CREATE INDEX idx_email_name ON customers (email, name);
-- SELECT name FROM customers WHERE email = '...'
-- → satisfied entirely from the secondary index, no bookmark lookup needed

Composite Index

-- Index: (status, created_at, user_id)
CREATE INDEX idx_orders_status_date_user
    ON orders(status, created_at, user_id);

-- Uses index (status equality + created_at range = both usable)
EXPLAIN SELECT * FROM orders
WHERE status = 'PAID' AND created_at > '2025-01-01';
-- → type: range, key: idx_orders_status_date_user

-- CANNOT use index — skips leftmost column
EXPLAIN SELECT * FROM orders WHERE created_at > '2025-01-01';
-- → type: ALL (full table scan)

-- Uses only the first column (range on 2nd → 3rd column unused in range scan)
EXPLAIN SELECT * FROM orders
WHERE status = 'PAID' AND created_at > '2025-01-01' AND user_id = 42;
-- status: range scan; created_at used in range; user_id NOT used for range
-- (user_id may still be used for filtering via index condition pushdown)

Covering Index

-- Query 1: only needs columns in the index
-- Index: (status, created_at)
SELECT id, status, created_at          -- id = PK, already in every secondary index
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC;
-- EXPLAIN → Extra: Using index  ✓ (PK included implicitly)

-- Query 2: needs 'total' — not in the index → row lookup required
SELECT id, status, created_at, total   -- total NOT in index
FROM orders
WHERE status = 'PAID';
-- EXPLAIN → Extra: (no "Using index") → performs row lookup for each row

-- Fix: create a covering index that includes 'total'
CREATE INDEX idx_orders_covering
    ON orders(status, created_at, total);
-- Now: EXPLAIN → Extra: Using index  ✓

Full-Text Index

-- Create table with full-text index
CREATE TABLE articles (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(200),
    body    TEXT,
    FULLTEXT idx_ft_title_body (title, body)  -- multi-column full-text index
);

-- Add to existing table
ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_body (body);

-- Natural Language mode (default) — ranked by relevance
SELECT id, title, MATCH(title, body) AGAINST ('kafka streaming') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST ('kafka streaming')  -- WHERE uses index
ORDER BY relevance DESC
LIMIT 10;

-- EXPLAIN should show: Extra = "Using where; Ft_hints: sorted"
EXPLAIN SELECT * FROM articles
WHERE MATCH(title, body) AGAINST ('kafka streaming');

-- Minimum token length (default 3 — cannot search 2-char words)
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';  -- default: 3
-- To allow 2-char words: set innodb_ft_min_token_size=2 in my.cnf + OPTIMIZE TABLE

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;