00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Create Index

Last updated: 5 min read

Creating the right index can turn a query that scans every row into one that reads a handful. This tutorial covers every way to create an index in MySQL, from a simple single-column index to composite, prefix, descending, functional and invisible indexes, and shows how to confirm with EXPLAIN that MySQL uses it.

1. Syntax

syntax.sql 
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  ON table_name (key_part [ASC | DESC], ...)
  [ALGORITHM = {INPLACE | COPY}] [LOCK = {NONE | SHARED | EXCLUSIVE}]
  [INVISIBLE];

-- key_part: column_name [(length)]  |  (expression)

ALTER TABLE table_name ADD INDEX index_name (key_part, ...);
Name indexes clearly, for example idx_city, idx_city_signup, uq_email. Names appear in EXPLAIN output and are needed to drop the index.

2. A Single-Column Index

query.sql 
EXPLAIN SELECT id FROM customers WHERE last_name = 'Nair';

Before: full scan

typekeyrows
ALLNULL4916
create.sql 
CREATE INDEX idx_last_name ON customers (last_name);
ANALYZE TABLE customers;
EXPLAIN SELECT id FROM customers WHERE last_name = 'Nair';

After: index lookup

typekeyrows
refidx_last_name500

3. Composite (Multi-Column) Indexes

Choosing columns for an indexStart from a slow query. Columns compared with equals go first in the index, the range or sort column goes last, and adding the selected columns can make the index covering.SELECT first_name, spend FROM customers WHERE city = 'Goa' AND signup_on >= '2025-01-01' ORDER BY signup_on1. equality firstcity2. range / sort lastsignup_on3. optional: cover+ first_name, spendCREATE INDEX idx_city_signup ON customers (city, signup_on);
Figure 1: design an index from the query: equality columns first, then the range or ORDER BY column.
composite.sql 
CREATE INDEX idx_city_signup ON customers (city, signup_on);
ANALYZE TABLE customers;
EXPLAIN SELECT first_name, spend FROM customers
WHERE city = 'Goa' AND signup_on >= '2025-01-01'
ORDER BY signup_on;

Result: 1 row

typekeykey_lenrowsExtra
rangeidx_city_signup85112Using index condition

No Using filesort: within one city the index is already sorted by signup_on.

4. Indexes in CREATE TABLE and ALTER TABLE

in-table.sql 
CREATE TABLE tickets (
  ticket_id   INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  status      VARCHAR(10) NOT NULL,
  opened_at   DATETIME NOT NULL,
  INDEX idx_customer (customer_id),
  INDEX idx_status_opened (status, opened_at)
);
ALTER TABLE tickets ADD INDEX idx_opened (opened_at), ADD UNIQUE INDEX uq_customer_opened (customer_id, opened_at);
SHOW INDEX FROM tickets;

Result: 7 rows

Key_nameSeq_in_indexColumn_nameNon_unique
PRIMARY1ticket_id0
uq_customer_opened1customer_id0
uq_customer_opened2opened_at0
idx_customer1customer_id1
idx_status_opened1status1
idx_status_opened2opened_at1
idx_opened1opened_at1

5. Descending Indexes

Since MySQL 8.0, DESC in an index is real. It helps queries that sort columns in mixed directions:

desc.sql 
CREATE INDEX idx_city_spend_desc ON customers (city ASC, spend DESC);
ANALYZE TABLE customers;
EXPLAIN SELECT first_name, spend FROM customers WHERE city = 'Pune' ORDER BY spend DESC LIMIT 5;

Result: 1 row

typekeyrowsExtra
refidx_city_spend_desc416NULL

6. Prefix Indexes for Long Strings

Index only the first N characters of a long column to save space. Choose N so the prefix is still nearly unique:

prefix-selectivity.sql 
SELECT COUNT(DISTINCT LEFT(email, 6))  AS distinct_6,
       COUNT(DISTINCT LEFT(email, 10)) AS distinct_10,
       COUNT(DISTINCT email)           AS distinct_full
FROM customers;

Result: 1 row

distinct_6distinct_10distinct_full
9950005000
prefix.sql 
CREATE INDEX idx_email_prefix ON customers (email(10));
ANALYZE TABLE customers;
EXPLAIN SELECT id FROM customers WHERE email = 'user4242@mail.example';

Result: 1 row

typekeykey_lenrows
refidx_email_prefix421
A prefix index can't be a covering index and can't be used for ORDER BY on the full value.

7. Functional (Expression) Indexes

Index the result of an expression (MySQL 8.0.13+), so conditions on that expression can use it. Note the extra parentheses:

functional.sql 
CREATE INDEX idx_signup_year ON customers ((YEAR(signup_on)));
ANALYZE TABLE customers;
EXPLAIN SELECT COUNT(*) FROM customers WHERE YEAR(signup_on) = 2024;

Result: 1 row

typekeyrows
refidx_signup_year1211

8. Invisible Indexes

An invisible index is maintained but ignored by the optimizer, which is perfect for testing whether an index is still needed before dropping it:

invisible.sql 
ALTER TABLE customers ALTER INDEX idx_last_name INVISIBLE;
EXPLAIN SELECT id FROM customers WHERE last_name = 'Nair';

Result: 1 row

typekeyrows
ALLNULL5140
query.sql 
ALTER TABLE customers ALTER INDEX idx_last_name VISIBLE;
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

9. Creating Indexes on Large Tables

  • InnoDB builds secondary indexes online (ALGORITHM=INPLACE, LOCK=NONE): reads and writes continue while the index builds.
  • It still takes CPU, I/O and temporary disk space, so build big indexes at quiet times.
  • Several indexes in one ALTER TABLE are built in a single pass.
  • Changing the primary key rebuilds the whole table.
query.sql 
CREATE INDEX idx_spend ON customers (spend) ALGORITHM = INPLACE LOCK = NONE;
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

10. Common Errors

errors.sql 
CREATE INDEX idx_spend ON customers (spend);
CREATE INDEX idx_bad ON customers (no_such_column);
Output 
ERROR 1061 (42000): Duplicate key name 'idx_spend'
ERROR 1072 (42000): Key column 'no_such_column' doesn't exist in table

Creating a second index on the same columns with a different name works, but it's just wasted space; MySQL warns about duplicate indexes:

query.sql 
CREATE INDEX idx_spend_copy ON customers (spend);
SHOW WARNINGS;
Output 
Query OK, 0 rows affected, 1 warning
Records: 0  Duplicates: 0  Warnings: 1

Result: 1 row

LevelCodeMessage
Warning1831Duplicate index 'idx_spend_copy' defined on the table 'crm.customers'. This is deprecated and will be disallowed in a future release.

11. Practice Exercises

Exercise 1: Create an index that serves WHERE first_name = ? AND last_name = ? and check it.
solution.sql 
CREATE INDEX idx_first_last ON customers (first_name, last_name);
ANALYZE TABLE customers;
EXPLAIN SELECT id FROM customers WHERE first_name = 'Gita' AND last_name = 'Bose';

Result: 1 row

typekeyrows
refidx_first_last1
Exercise 2: Create a functional index for case-insensitive email lookups with LOWER().
solution.sql 
CREATE INDEX idx_email_lower ON customers ((LOWER(email)));
ANALYZE TABLE customers;
EXPLAIN SELECT id FROM customers WHERE LOWER(email) = 'user77@mail.example';

Result: 1 row

typekeyrows
refidx_email_lower1

12. Summary

  • CREATE INDEX name ON table (cols); or ALTER TABLE … ADD INDEX creates an index.
  • Composite indexes: equality columns first, range/sort column last.
  • Use DESC, prefix, functional and invisible indexes where they fit.
  • Run ANALYZE TABLE and EXPLAIN to confirm the index is used.
  • InnoDB builds indexes online, but avoid duplicates and build large ones at quiet times.