Advanced Indexing Strategies
From "which column do I index?" to "which index does the optimizer actually use, and why?"
Big idea: A single-column index is easy. The real skill is composing indexes so the optimizer can use them: the order of columns in a composite index decides which queries it accelerates, a covering index lets a query skip the table entirely, and a predicate written the wrong way can silently disable an index that exists. This lesson is about writing queries and indexes that cooperate.
By the end you will be able to:
- design a composite index and apply the leftmost-prefix rule,
- build a covering index so a query runs as an index-only scan,
- reason about selectivity and why the optimizer sometimes ignores an index,
- use a functional (expression) index in MySQL 8,
- spot and rewrite non-sargable predicates that kill an index.
This is the advanced course: you already know that a B-tree index speeds up lookups and range scans, that a PRIMARY KEY is a clustered index in InnoDB, and how to read an EXPLAIN plan. We build directly on that.
0. The example database
Every query below runs against one small shop schema. Picture each table as if it held millions of rows - indexing only matters at scale - but we show a handful so results stay readable.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
last_name VARCHAR(50),
first_name VARCHAR(50),
city VARCHAR(50),
email VARCHAR(120)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(20),
total DECIMAL(10,2)
);
INSERT INTO customers VALUES
(1,'Smith','Anna','Berlin','anna@shop.io'),
(2,'Smith','Ben','Berlin','ben@gmail.com'),
(3,'Jones','Cara','Almaty','cara@mail.kg'),
(4,'Adams','Dan','Berlin','dan@gmail.com');
INSERT INTO orders VALUES
(10,1,'2024-02-11','shipped', 42.00),
(11,1,'2024-03-02','shipped', 18.50),
(12,2,'2024-02-20','pending', 99.00),
(13,3,'2023-12-30','shipped', 7.25),
(14,4,'2024-05-05','cancelled',60.00);Two tables, a one-to-many relationship (orders.customer_id references customers.customer_id), and columns with very different selectivity - status has a handful of values, email is nearly unique. Both facts matter below.