TopolearnIndexes are the main performance lever in MySQL, and InnoDB's clustered layout changes how to design them.
All InnoDB indexes are B+trees. Beyond the clustered primary key you add secondary indexes: CREATE INDEX idx_name ON t (col) or KEY idx_name (col) in CREATE TABLE.
MySQL-specific points:
- Composite indexes follow the leftmost-prefix rule:
(customer_id, created_at)servesWHERE customer_id = ?andWHERE customer_id = ? ORDER BY created_at. Because the primary key is appended to every secondary index,(customer_id)already behaves like(customer_id, id). - Covering: if the index contains every column the query needs,
EXPLAINshowsUsing indexand the clustered index isn't touched. - Prefix indexes index only the first N characters:
KEY (url(100)). Needed forTEXT/BLOBcolumns; they can't cover queries. - Functional indexes (8.0.13+) index an expression; note the double parentheses:
INDEX ((LOWER(email))). - Descending indexes (8.0) help mixed-order sorts like
ORDER BY a ASC, b DESC. - Invisible indexes:
ALTER TABLE t ALTER INDEX idx INVISIBLEhides an index from the optimizer while still maintaining it. Test the effect of dropping an index safely, then drop it or make it visible again instantly.
Find unused indexes with sys.schema_unused_indexes and duplicates with sys.schema_redundant_indexes; each extra index costs writes and buffer pool space.
Example
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT UNSIGNED NOT NULL,
email VARCHAR(255) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_cust_created (customer_id, created_at),
INDEX idx_email_lower ((LOWER(email))) -- functional index
);
-- served by idx_cust_created, no filesort
EXPLAIN SELECT id, created_at FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;
-- key: idx_cust_created Extra includes: Backward index scan; Using index
EXPLAIN SELECT id FROM orders WHERE LOWER(email) = 'ana@example.com';
-- key: idx_email_lower
ALTER TABLE orders ALTER INDEX idx_email_lower INVISIBLE; -- test before dropping
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = DATABASE();Remember
- Composite indexes follow the leftmost-prefix rule; the PK is implicitly appended
- Functional indexes need double parentheses: INDEX ((expr))
- Make an index INVISIBLE to test dropping it safely
Watch out for
- Adding (customer_id) when (customer_id, created_at) already exists: the first is redundant.