Indexes in MySQL

InnoDB stores every table as a B+tree clustered on the primary key: the leaf pages of the primary-key index contain the full rows.

Concept

InnoDB stores every table as a B+tree clustered on the primary key: the leaf pages of the primary-key index contain the full rows. Every secondary index is a separate B+tree whose leaves contain the indexed columns plus the primary key value, which is used to find the full row.

Index types in MySQL:

Primary key (clustered) PRIMARY KEY (col) Row storage order; must be unique, NOT NULL Secondary (B-tree) INDEX ix (col) / KEY Filters, joins, sorting UNIQUE INDEX ux (col) Uniqueness + lookup INDEX ix (a, b, c)

Multi-column filters/sorts (leftmost prefix) INDEX ix (col(20)) Long strings, TEXT/BLOB INDEX ix (a ASC, b DESC) Mixed-direction ORDER BY (8.0+) INDEX ix ((LOWER(email))) Index on an expression (8.0.13+) ALTER INDEX ix INVISIBLE Test dropping an index safely (8.0+)

INDEX ix ((CAST(doc->'$.tags' AS CHAR(20) ARRAY))) JSON arrays (8.0.17+) FULLTEXT INDEX ft (title, body) Text search (Module 24) SPATIAL INDEX sp (geo) Geometry (Module 25)

MEMORY engine only (InnoDB builds an internal adaptive hash index automatically)

MySQL does not support partial/filtered indexes or INCLUDE columns; add columns to the index key instead for covering.

flowchart TD
    Q["SELECT * FROM orders WHERE customer_id = 2"] --> S[Secondary index ix_orders_customer<br/>B+tree on customer_id]
    S -->|leaf entries: customer_id=2 → PK 1002, 1006| P[Clustered index PRIMARY<br/>B+tree on order_id]
    P -->|leaf pages hold full rows| R[(Rows 1002 and 1006)]

Why It Matters

Indexes turn full scans of millions of rows into a few page reads. Poor or missing indexes are the number-one cause of slow MySQL applications; too many indexes slow writes and waste buffer-pool memory.

Syntax

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX ix_name ON tbl (col1 [ASC|DESC], col2(prefix), (expr));
ALTER TABLE tbl ADD INDEX ix_name (cols);
ALTER TABLE tbl DROP INDEX ix_name;
DROP INDEX ix_name ON tbl;
SHOW INDEX FROM tbl;
ALTER TABLE tbl ALTER INDEX ix_name INVISIBLE;   -- / VISIBLE
ANALYZE TABLE tbl;                                -- refresh index statistics

Example

16.1 Creating and using indexes Illustrative output:

Using index means a covering index: order_id is the primary key and is automatically present in every secondary index, so no row lookup is needed.

16.2 Leftmost-prefix rule for composite indexes Index (status, order_date):

status = 'X' status = 'X' AND order_date >=... Yes, both columns status = 'X' ORDER BY order_date Yes, no filesort order_date >=... only

Not for seeking (possibly a full index scan; 8.0.13+ may use skip scan when the first column has few values)

status IN ('A','B') AND order_date =... Yes (range on status)

Order columns: equality first, then range/sort.

16.3 Prefix indexes Useful for long VARCHAR/TEXT columns; cannot be covering and cannot help ORDER BY fully. Choose the prefix length by checking selectivity:

16.4 Functional indexes (8.0.13+) The query expression must match the indexed expression exactly. (A range on the bare column is usually simpler: order_date >= '2026-03-01' AND order_date < '2026-04-01'.) 16.5 Descending indexes (8.0+)

16.6 Invisible indexes (safe index removal) 16.7 Primary key design for InnoDB

Keep the PK short (every secondary index stores it).

Prefer monotonically increasing keys (AUTO_INCREMENT, UUIDv7/UUID_TO_BIN(UUID(), 1)) so inserts append to the end of the B-tree; random UUIDs cause page splits and fragmentation.

Never update PK values (it moves the row).

16.8 When indexes help and hurt Help: selective WHERE conditions, join columns (FKs), ORDER BY/GROUP BY with LIMIT, MIN/MAX, covering reads.

Hurt: every INSERT/UPDATE/DELETE maintains every index; low-selectivity columns alone (a status with 3 values); redundant indexes ((a) when (a, b) exists); unused indexes.

Find unused and redundant indexes with the sys schema:

16.9 Statistics and histograms Histograms help the optimizer estimate selectivity of non-indexed columns without the maintenance cost of an index.

16.10 Non-sargable predicates

CREATE INDEX ix_orders_status_date ON orders (status, order_date);

EXPLAIN SELECT order_id, order_date FROM orders
WHERE status = 'DELIVERED' AND order_date >= '2026-01-01'
ORDER BY order_date;

+----+-------------+--------+-------+------------------------+------------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys          | key                    | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+------------------------+------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | orders | range | ix_orders_status_date  | ix_orders_status_date  | 85      | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+------------------------+------------------------+---------+------+------+--------------------------+

CREATE INDEX ix_customers_email_prefix ON customers (email(20));

SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10,
       COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS sel20,
       COUNT(DISTINCT email) / COUNT(*)           AS full_sel
FROM customers;

CREATE INDEX ix_orders_month ON orders ((DATE_FORMAT(order_date, '%Y-%m')));
SELECT COUNT(*) FROM orders WHERE DATE_FORMAT(order_date, '%Y-%m') = '2026-03';   -- can use ix_orders_month

CREATE INDEX ix_customers_lower_email ON customers ((LOWER(email)));

CREATE INDEX ix_dtc_vehicle_time ON dtc_events (vehicle_id ASC, detected_at DESC);
SELECT * FROM dtc_events WHERE vehicle_id = 7 ORDER BY detected_at DESC LIMIT 20;   -- no sort needed

ALTER TABLE orders ALTER INDEX ix_orders_status_date INVISIBLE;   -- optimizer ignores it; still maintained
-- monitor performance for a few days...
ALTER TABLE orders ALTER INDEX ix_orders_status_date VISIBLE;     -- instant rollback
DROP INDEX ix_orders_status_date ON orders;                       -- when confident

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'eh_company';
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'eh_company';

ANALYZE TABLE orders;                                          -- refresh index statistics
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;   -- histogram for a non-indexed column (8.0+)
SELECT * FROM information_schema.column_statistics WHERE table_name = 'orders';

WHERE YEAR(order_date) = 2026                 -- cannot seek on ix(order_date)
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'   -- can

WHERE phone = 9876543210                      -- phone VARCHAR: numeric comparison, index unusable
WHERE phone = '9876543210'                    -- correct

Expected Result

(Several columns omitted; cardinality values are estimates.)

SHOW INDEX FROM orders;

+--------+------------+-----------------------+--------------+-------------+-----------+-------------+---------+
| Table  | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Visible |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+---------+
| orders |          0 | PRIMARY               |            1 | order_id    | A         |           6 | YES     |
| orders |          1 | ix_orders_customer    |            1 | customer_id | A         |           4 | YES     |
| orders |          1 | ix_orders_status_date |            1 | status      | A         |           4 | YES     |
| orders |          1 | ix_orders_status_date |            2 | order_date  | A         |           6 | YES     |
+--------+------------+-----------------------+--------------+-------------+-----------+-------------+---------+

Common Mistakes

Random UUID primary keys stored as CHAR(36).

One single-column index per column instead of the right composite index.

Wrong composite order.

Functions or type conversions on indexed columns.

Forgetting that InnoDB secondary indexes already include the PK (no need to append it).

Assuming CREATE INDEX on a huge table is free (it uses I/O and can lag replicas).

Best Practices

Design indexes from real queries; verify with EXPLAIN.

Short, increasing primary keys.

Use invisible indexes before dropping.

Periodically review sys.schema_unused_indexes and schema_redundant_indexes.

Interview Relevance

Very high: "What is a clustered index in InnoDB?", "Why should the PK be short?", "Leftmost prefix rule?", "What is a covering index?", "Why is my index not used?", "Invisible index?", "Functional index?".

Practice Questions

Create the best index for SELECT * FROM dtc_events WHERE vehicle_id = ? AND status = 'ACTIVE' ORDER BY detected_at DESC LIMIT 10.

Explain why (customer_id) becomes redundant after creating (customer_id, order_date).

Make an index invisible, check EXPLAIN, and make it visible again.

Create a functional index for case-insensitive search on a _bin collated column.