How to Create an Index in MySQL (With Examples)

Introduction

Indexes are the single most effective way to speed up MySQL queries. Without an index, MySQL scans every row in the table (full table scan). With the right index, it jumps directly to matching rows.

Create a Basic Index

A single-column index speeds up queries that filter or sort by that column.

-- Create index on a single column:
CREATE INDEX idx_users_email ON users (email);

-- Verify the index was created:
SHOW INDEX FROM users;

Create a Composite (Multi-Column) Index

Composite indexes cover queries that filter on multiple columns. Column order matters — put the most selective column first.

-- Index on two columns:
CREATE INDEX idx_orders_status_date
ON orders (status, created_at);

-- This index helps queries like:
SELECT * FROM orders
WHERE status = 'shipped'
AND created_at > '2026-01-01';

Create a Unique Index

A unique index enforces that no two rows can have the same value in the indexed column(s).

-- Unique index (prevents duplicates):
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);

-- This also works as a constraint:
-- INSERT with duplicate email will fail with Error 1062

Create a Full-Text Index

Full-text indexes enable natural language search on text columns. Much faster than LIKE "%keyword%" for large text fields.

-- Full-text index for search:
CREATE FULLTEXT INDEX idx_posts_content
ON posts (title, body);

-- Use it with MATCH AGAINST:
SELECT * FROM posts
WHERE MATCH(title, body)
AGAINST('mysql performance' IN NATURAL LANGUAGE MODE);

Add Index to Existing Table with ALTER TABLE

You can also add indexes using ALTER TABLE syntax, which is useful in migrations.

-- Add index via ALTER TABLE:
ALTER TABLE users ADD INDEX idx_users_country (country);

-- Add unique index:
ALTER TABLE users ADD UNIQUE INDEX idx_users_username (username);

-- Drop an index:
ALTER TABLE users DROP INDEX idx_users_country;

Check Query Performance with EXPLAIN

Use EXPLAIN to verify your indexes are being used.

-- Check if a query uses an index:
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Look for:
-- type: ref or const (good) vs ALL (full scan = bad)
-- key: should show your index name
-- rows: should be small number, not total table rows

Best Practices

• Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses
• Don't over-index — each index slows down INSERT/UPDATE/DELETE operations
• Remove unused indexes with DROP INDEX to save disk space and write performance
• Use EXPLAIN regularly to verify indexes are being used
• For large tables, consider partial indexes or covering indexes

Generate MySQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct MySQL query instantly.

Frequently Asked Questions