MySQL Indexes Explained: Types, Examples, and Best Practices

An index is a data structure (B-Tree by default) that speeds up data retrieval. Think of it as a book's index: instead of flipping every page, you check the index and jump straight to the page.

Table of Contents

• How an Index Works
• Creating an Index
• Unique Index
• Composite Index
• Full-Text Index
• Seeing and Removing Indexes
• Index Best Practices

How an Index Works

Without an index, MySQL scans every row in the table (a full table scan) to find matching data. With an index, MySQL navigates a sorted structure to locate rows in logarithmic time — exponentially faster on large tables.

The trade-off: indexes speed up SELECT, UPDATE, and DELETE but slow down INSERT because the index must be updated on every write.

Creating an Index

Basic Syntax

CREATE INDEX index_name ON table_name (column_name);

You can also create an index when defining the table:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    last_name VARCHAR(50),
    INDEX idx_last_name (last_name)
);

Example

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100),
    username VARCHAR(50)
);

-- Insert sample data
INSERT INTO users (email, username) VALUES
('alice@example.com', 'alice'),
('bob@example.com', 'bob'),
('charlie@example.com', 'charlie');

-- Before the index, this query does a full scan:
SELECT * FROM users WHERE username = 'bob';

-- Add the index
CREATE INDEX idx_username ON users (username);

-- Now MySQL uses the index. Much faster on large tables.
SELECT * FROM users WHERE username = 'bob';

Use EXPLAIN to check whether a query uses an index:

EXPLAIN SELECT * FROM users WHERE username = 'bob';
-- Look for "possible_keys" and "key" columns in the output.

Unique Index

A unique index ensures all values in a column are distinct. It works like a UNIQUE constraint — in fact, when you add a UNIQUE constraint, MySQL creates a unique index automatically.

CREATE UNIQUE INDEX idx_email_unique ON users (email);

Or as a column constraint:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

Trying to insert a duplicate returns an error:

INSERT INTO users (email, username) VALUES ('alice@example.com', 'alice2');
-- ERROR 1062: Duplicate entry 'alice@example.com' for key 'users.idx_email_unique'

When to use it: any column that should not have duplicates — email addresses, usernames, ID numbers, order references.

Composite Index

A composite index spans multiple columns. MySQL uses it for queries that filter on some or all of those columns, but the column order matters.

CREATE INDEX idx_name_dept ON employees (last_name, department_id);

How MySQL Uses It

This index helps queries that filter by:

• last_name alone — yes, because last_name is the leftmost column.
• last_name + department_id — yes.
• department_id alone — no, because department_id is not the leftmost prefix.

This is called the leftmost prefix rule. MySQL can only use parts of a composite index from left to right without skipping columns.

Column Order Guidelines

1. Put the most selective column first (the one with the most unique values).
2. Put columns used in WHERE before columns used in ORDER BY.
3. Don't index every column combination — a well-designed composite index covers multiple query patterns.

Example

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    status VARCHAR(20),
    created_at DATE
);

-- Covers: WHERE customer_id = ? and WHERE customer_id = ? AND status = ?
CREATE INDEX idx_customer_status ON orders (customer_id, status);

Full-Text Index

A full-text index is designed for searching text content — words, phrases, and natural language. Unlike a regular index (which works on exact values or prefixes), a full-text index understands word boundaries.

Creating a Full-Text Index

CREATE FULLTEXT INDEX idx_content ON articles (title, body);

Or in the table definition:

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
);

Searching with MATCH ... AGAINST

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization');

The columns in MATCH must match the columns in the full-text index definition.

Search Modes

Natural Language Mode (default) — ranks results by relevance:

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database performance');

Boolean Mode — supports operators like + (must include) and - (must exclude):

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+database -mysql' IN BOOLEAN MODE);

When to use it: any application that needs search — blog post search, product catalog search, documentation search. For production search at scale, consider dedicated search engines (Elasticsearch, Meilisearch), but for most applications MySQL full-text is enough.

Limitations

• Only works with MyISAM and InnoDB storage engines.
• Ignores words shorter than a minimum length (default 3 or 4 characters, configurable via ft_min_word_len).
• Has a stopword list (common words like "the", "and" are ignored).
• Not ideal for CJK (Chinese, Japanese, Korean) text without additional configuration.

Seeing and Removing Indexes

List Indexes on a Table

SHOW INDEXES FROM users;

Drop an Index

DROP INDEX idx_username ON users;

Example

-- Check what indexes exist
SHOW INDEXES FROM users;

-- Remove an unused index
DROP INDEX idx_username ON users;

-- Confirm it's gone
SHOW INDEXES FROM users;

Index Best Practices

Do

• Index columns used in WHERE, JOIN, and ORDER BY. These are the queries that benefit most.
• Use EXPLAIN to verify your indexes are actually being used.
• Monitor slow query logs to find queries that need indexing.
• Drop duplicate or unused indexes. They waste disk space and slow down writes.
• Keep indexes narrow. An index on VARCHAR(255) is larger and slower than one on VARCHAR(50).
• Consider composite indexes for queries that filter on multiple columns.

Don't

• Don't index every column. Each index adds overhead on INSERT, UPDATE, and DELETE.
• Don't create separate indexes on each column if a composite index covers your queries better.
• Don't index columns with low cardinality (like a gender column with only 'M' and 'F'). The index won't help much.
• Don't index columns you never search on.
• Don't over-index small tables. A table with a few hundred rows doesn't need indexes — a full table scan is faster than the index lookup overhead.

Quick Decision Guide