MySQL CREATE INDEX, DROP INDEX and SHOW INDEX

Three ways to create an index

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(20) NOT NULL,
    name VARCHAR(80) NOT NULL,
    category VARCHAR(20) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    created_at DATETIME NOT NULL
);
INSERT INTO products (sku, name, category, price, created_at) VALUES
    ('NB-01', 'Notebook', 'stationery', 80, '2026-01-10 09:00:00'),
    ('GP-02', 'Gel Pen', 'stationery', 15.5, '2026-01-12 10:00:00'),
    ('BP-03', 'Backpack', 'bags', 999, '2026-02-01 11:00:00'),
    ('DL-04', 'Desk Lamp', 'home', 650, '2026-03-03 12:00:00');

1. CREATE INDEX:

query.sql

SQL

CREATE INDEX idx_products_category ON products (category);

2. ALTER TABLE … ADD INDEX (the same thing, and you can combine several changes in one statement):

query.sql

SQL

ALTER TABLE products ADD INDEX idx_products_created (created_at), ADD UNIQUE INDEX uq_products_sku (sku);

3. In CREATE TABLE:

query.sql

SQL

CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    slug VARCHAR(30) NOT NULL,
    UNIQUE KEY uq_tags_slug (slug),
    KEY idx_tags_name (name)
);

KEY and INDEX mean the same thing in MySQL.

The syntax

Text

PLAIN

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [USING BTREE | HASH]
    ON table_name (column [(prefix_length)] [ASC | DESC], ...)
    [INVISIBLE | VISIBLE]
    [COMMENT 'text'];

Naming

Give indexes meaningful names. A convention that works:

Index	Name
Normal	`idx_table_column`
Unique	`uq_table_column`
Composite	`idx_table_col1_col2`
Foreign key	`fk_child_parent` (MySQL creates the index with that name)

If you leave the name out, MySQL names it after the first column (category, category_2). Names matter when you must drop or hint at an index.

SHOW INDEX

query.sql

SQL

SHOW INDEX FROM products;

Output

OUTPUT

+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY               |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          0 | uq_products_sku       |            1 | sku         | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_products_category |            1 | category    | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_products_created  |            1 | created_at  | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set

The important columns:

Column	Meaning
`Key_name`	The index name (`PRIMARY` for the primary key)
`Non_unique`	0 for unique indexes, 1 otherwise
`Seq_in_index`	The position of the column inside a multi-column index (1, 2, …)
`Column_name`	The indexed column
`Cardinality`	The estimated number of different values (high is good for filtering)
`Sub_part`	The prefix length, for prefix indexes
`Null`	`YES` if the column may hold `NULL`
`Index_type`	`BTREE`, `HASH`, `FULLTEXT`, `SPATIAL`
`Visible`	`YES` or `NO` (invisible index)

A more compact view:

query.sql

SQL

SELECT index_name, non_unique, seq_in_index, column_name
FROM information_schema.statistics
WHERE table_schema = 'learn' AND table_name = 'products'
ORDER BY index_name, seq_in_index;

Output

OUTPUT

+-----------------------+------------+--------------+-------------+
| INDEX_NAME            | NON_UNIQUE | SEQ_IN_INDEX | COLUMN_NAME |
+-----------------------+------------+--------------+-------------+
| idx_products_category |          1 |            1 | category    |
| idx_products_created  |          1 |            1 | created_at  |
| PRIMARY               |          0 |            1 | id          |
| uq_products_sku       |          0 |            1 | sku         |
+-----------------------+------------+--------------+-------------+
4 rows in set

DROP INDEX

query.sql

SQL

1
2
3

DROP INDEX idx_products_category ON products;
-- or: ALTER TABLE products DROP INDEX idx_products_category;
SELECT index_name FROM information_schema.statistics WHERE table_schema = 'learn' AND table_name = 'products' ORDER BY index_name;

Output

OUTPUT

+----------------------+
| INDEX_NAME           |
+----------------------+
| idx_products_created |
| PRIMARY              |
| uq_products_sku      |
+----------------------+
3 rows in set

Dropping an index is quick. Before dropping one in production, check that no query depends on it (see Invisible Indexes).

Rename an index

query.sql

SQL

ALTER TABLE products RENAME INDEX idx_products_created TO idx_products_created_at;

Building an index on a big table

Creating an index on a table with millions of rows reads the whole table and sorts the values. For InnoDB this is an online operation, so reads and writes continue (ALGORITHM=INPLACE, LOCK=NONE):

query.sql

SQL

ALTER TABLE products ADD INDEX idx_products_price (price), ALGORITHM = INPLACE, LOCK = NONE;

It still uses CPU, I/O and disk space, and it holds a brief metadata lock at the start and the end. Do it in a quiet period, and expect it to take minutes on very large tables. See Online DDL.

Keep statistics fresh

MySQL decides whether to use an index from statistics. After a bulk load or a large change:

query.sql

SQL

ANALYZE TABLE products;

Output

OUTPUT

+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| learn.products | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set

Limits

Up to 64 indexes per InnoDB table, and 16 columns per index.
A key length of at most 3072 bytes (with DYNAMIC row format). A VARCHAR(1000) in utf8mb4 can exceed it. Use a prefix (Prefix Indexes).
TEXT and BLOB columns need a prefix length, or a FULLTEXT index.

lightbulb

Create indexes in migrations

Put every CREATE INDEX in a version-controlled migration, with the query that needs it in a comment. Six months later you will know why it exists.