INDEX
, and, EXPLAIN
Syntax
CREATE INDEX index_name ON table (column1 [, column2 ...]); ALTER TABLE table ADD INDEX index_name (columns); DROP INDEX index_name ON table; EXPLAIN SELECT ...;
Parameters
| Parameters | Description | Examples | Level |
|---|---|---|---|
CREATE INDEX |
CREATE INDEX idx_email ON users(email); |
Common | |
UNIQUE INDEX |
CREATE UNIQUE INDEX uk_phone ON users(phone); |
Common | |
SHOW INDEX |
SHOW INDEX FROM users; |
Common | |
DROP INDEX |
DROP INDEX idx_email ON users; |
Common | |
EXPLAIN |
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com'; |
Common | |
FULLTEXT |
CREATE FULLTEXT INDEX ft_title ON articles(title, content); |
Advanced |
Examples
-- CREATE INDEX idx_status_created ON orders(status, created_at);
status status+created_at
EXPLAIN
EXPLAIN SELECT o.*, u.username FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 1 ORDER BY o.created_at DESC LIMIT 20;
type( ref/range), key( ), rows()
SHOW INDEX FROM orders; -- SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'orders';
CARDINALITY Description
--, CREATE INDEX idx_cover_order ON orders(user_id, status, created_at, total_amount); -- SELECT status, created_at, total_amount FROM orders WHERE user_id = 1001;
EXPLAIN Extra Using index
Common Errors
ERROR 1061 (42000): Duplicate key name 'idx_xxx',
EXPLAIN type=ALL(). WHERE,
Tips
- ,
- : (a,b,c) WHERE a=1 and WHERE a=1 AND b=2
- WHERE : WHERE YEAR(created_at) = 2026