Indexes

-- 1) Single-column index
CREATE INDEX idx_users_email ON users(email);

-- Unique
CREATE UNIQUE INDEX uq_users_email ON users(email);
-- Or as a constraint:
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 2) Composite index — leftmost-prefix rule
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at);

-- Helps queries with leading columns:
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';                     -- yes (prefix match)
SELECT * FROM orders WHERE user_id = 42;                                          -- yes
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at; -- yes (all three)
SELECT * FROM orders WHERE status = 'paid';                                       -- NO (skips leading column)

-- 3) Covering index — query reads only from the index, no table touch
CREATE INDEX idx_orders_cover ON orders(user_id, status, created_at, total);

EXPLAIN SELECT user_id, status, created_at, total
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC;
-- 'Using index' in Extra column = covering

-- 4) Primary key — clustered in InnoDB
-- Rows are physically sorted by PK; insert order = scan order if PK is monotonic.
-- Choose a small, sequential PK (BIGINT AUTO_INCREMENT, UUIDv7).
-- Avoid: random UUIDv4 — non-sequential, page-splits, write amplification.

-- 5) Foreign keys — auto-create an index by default (if no compatible one exists)
CREATE TABLE order_items (
    order_id BIGINT UNSIGNED NOT NULL,
    sku      VARCHAR(40) NOT NULL,
    qty      INT NOT NULL,
    INDEX idx_oi_order (order_id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- 6) Prefix index — for long strings (URLs, emails)
CREATE INDEX idx_users_url_prefix ON users(homepage(64));
-- Indexes first 64 chars only; trades selectivity for size.

-- 7) Fulltext index — substring search
CREATE FULLTEXT INDEX idx_posts_body ON posts(title, body);

SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('docker container' IN NATURAL LANGUAGE MODE);

SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('+docker -kubernetes' IN BOOLEAN MODE);

-- 8) Hash index — only on MEMORY engine (rarely used today)
-- For exact equality lookups; InnoDB has an internal 'adaptive hash index' but you don't manage it.

-- 9) Functional index (MySQL 8.0.13+)
CREATE INDEX idx_users_email_lower ON users((LOWER(email)));

SELECT * FROM users WHERE LOWER(email) = 'ada@example.com';

-- 10) Invisible index — test impact before committing
ALTER TABLE orders ALTER INDEX idx_orders_user_status_created INVISIBLE;
-- Query planner ignores it; run benchmarks; if perf is OK, drop it.
ALTER TABLE orders ALTER INDEX idx_orders_user_status_created VISIBLE;

-- 11) EXPLAIN — read the plan
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- Key columns:
-- type     : const < eq_ref < ref < range < index < ALL  (left is faster)
-- key      : which index was used
-- key_len  : how many bytes of the index were used
-- rows     : estimated rows scanned
-- Extra    : 'Using index' (covering), 'Using filesort' (no order index), 'Using temporary'

-- 12) ANALYZE / OPTIMIZE
ANALYZE TABLE orders;        -- refresh statistics for the optimiser
OPTIMIZE TABLE orders;       -- rebuild — defrag, free space (heavy)

-- 13) Show indexes
SHOW INDEX FROM orders;
SELECT * FROM information_schema.statistics WHERE table_name = 'orders';

-- 14) Drop unused indexes
-- Unused indexes cost write performance + disk + buffer pool.
-- Check sys.schema_unused_indexes (MySQL 5.7+):
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';

-- 15) Patterns that defeat indexes
--   • Functions on the indexed column without a functional index:
--       WHERE LOWER(email) = 'x'   (no functional index → table scan)
--   • Implicit type coercion:
--       WHERE id = '42'             (varchar → int) — sometimes ignores index
--   • Leading wildcard LIKE:
--       WHERE email LIKE '%@example.com'    — no index help; consider FULLTEXT
--   • OR across non-indexed columns:
--       WHERE a = 1 OR b = 2        — UNION ALL of two indexed queries often faster

-- 16) Choosing what to index
--   1. Index the columns in WHERE / JOIN / ORDER BY
--   2. Composite covering indexes for hot paths
--   3. Add UNIQUE for natural keys (email, slug, sku)
--   4. Never index a column with < 5% selectivity (gender, boolean) on its own
--   5. Measure: each index slows writes 5-15%

CREATE INDEX idx_orders_user ON orders (user_id);
CREATE UNIQUE INDEX uq_users_email ON users (email);
SHOW INDEX FROM users;