How to Create Indexes in MySQL: A Complete Guide

Indexes are one of the most powerful tools for optimizing database performance. In this guide, I'll walk you through everything you need to know about creating indexes in MySQL, from basic syntax to advanced optimization techniques.

What is a Database Index?

Think of a database index like the index in a book. Instead of reading every page to find a topic, you can look it up in the index and jump directly to the right page. Similarly, a MySQL index allows the database to find rows much faster without scanning the entire table.

Why Use Indexes?

Without indexes, MySQL must perform a full table scan, reading every row to find matches. With proper indexes:

• Query performance improves dramatically (sometimes by 100x or more)
• Sorting operations become faster
• JOIN operations are more efficient
• Unique constraints are enforced

However, indexes aren't free. They consume disk space and slow down INSERT, UPDATE, and DELETE operations since the index must also be updated.

Creating Your First Index

Basic Syntax

The most straightforward way to create an index:

CREATE INDEX index_name ON table_name (column_name);

Real-World Example

Let's say you have a users table and frequently search by email:

CREATE INDEX idx_email ON users (email);

Now queries like this will be much faster:

SELECT * FROM users WHERE email = '[e-mail]';

Types of Indexes in MySQL

1. Single-Column Index

Indexes on a single column:

CREATE INDEX idx_lastname ON users (last_name);

2. Composite Index (Multi-Column)

Indexes on multiple columns. Order matters!

CREATE INDEX idx_name ON users (last_name, first_name);

This index helps with queries filtering by:

• last_name alone
• last_name AND first_name together

But NOT queries filtering only by first_name (leftmost prefix rule).

3. Unique Index

Ensures all values in the indexed column(s) are unique:

CREATE UNIQUE INDEX idx_username ON users (username);

Attempts to insert duplicate usernames will fail.

4. Full-Text Index

For searching text content (available on VARCHAR, TEXT columns):

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

Query using MATCH...AGAINST:

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('mysql indexing' IN NATURAL LANGUAGE MODE);

5. Spatial Index

For geometric data types (POINT, LINESTRING, etc.):

CREATE SPATIAL INDEX idx_location ON stores (coordinates);

Creating Indexes: Different Methods

Method 1: CREATE INDEX Statement

CREATE INDEX idx_created_at ON posts (created_at);

Method 2: ALTER TABLE

ALTER TABLE posts ADD INDEX idx_created_at (created_at);

Method 3: During Table Creation

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    created_at DATETIME,
    INDEX idx_created_at (created_at)
);

Method 4: Adding Primary Key (Automatically Indexed)

ALTER TABLE posts ADD PRIMARY KEY (id);

Advanced Indexing Techniques

Index Prefixes

For long VARCHAR or TEXT columns, index only the first N characters:

CREATE INDEX idx_description ON products (description(50));

This saves space while still providing good selectivity for most queries.

Descending Indexes (MySQL 8.0+)

Optimize ORDER BY queries with mixed sort directions:

CREATE INDEX idx_date_score ON posts (created_at DESC, score ASC);

Perfect for queries like:

SELECT * FROM posts ORDER BY created_at DESC, score ASC;

Invisible Indexes (MySQL 8.0+)

Test the impact of removing an index without actually dropping it:

ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

If performance is fine, you can safely drop it. Otherwise, make it visible again:

ALTER TABLE users ALTER INDEX idx_email VISIBLE;

Best Practices

1. Index Columns Used in WHERE Clauses

-- Frequently run this query?
SELECT * FROM orders WHERE customer_id = 123;

-- Create this index
CREATE INDEX idx_customer_id ON orders (customer_id);

2. Index Foreign Keys

Always index foreign key columns used in JOINs:

CREATE INDEX idx_user_id ON posts (user_id);

3. Consider Composite Indexes for Multiple Filters

-- Query
SELECT * FROM products WHERE category_id = 5 AND price > 100;

-- Index
CREATE INDEX idx_category_price ON products (category_id, price);

4. Don't Over-Index

Each index has a cost. Avoid:

• Indexing every column
• Creating redundant indexes (if you have an index on (a, b, c), you don't need one on just (a))
• Indexing very small tables

5. Use Covering Indexes

A covering index includes all columns needed by a query:

-- Query only needs id, name, email
SELECT id, name, email FROM users WHERE status = 'active';

-- Covering index
CREATE INDEX idx_status_covering ON users (status, id, name, email);

This allows MySQL to satisfy the query entirely from the index without accessing the table.

Checking Your Indexes

View All Indexes on a Table

SHOW INDEX FROM users;

Explain Query Execution

See if your index is being used:

EXPLAIN SELECT * FROM users WHERE email = '[e-mail]';

Look for type: ref or type: index in the output, and check the key column to see which index is used.

Find Unused Indexes

SELECT * FROM sys.schema_unused_indexes;

Dropping Indexes

If an index isn't being used or is causing more harm than good:

DROP INDEX idx_email ON users;

Or using ALTER TABLE:

ALTER TABLE users DROP INDEX idx_email;

Common Pitfalls to Avoid

1. Indexing low-cardinality columns: Columns like gender or status with few distinct values often don't benefit much from indexes

2. Wrong column order in composite indexes: Put the most selective column first, and consider your query patterns

3. Using functions on indexed columns: This prevents index usage
   

   -- Bad: Won't use index
   SELECT * FROM users WHERE UPPER(email) = '[e-mail]';

   -- Good: Will use index
   SELECT * FROM users WHERE email = '[e-mail]';

1. Forgetting to analyze after creating indexes: Run ANALYZE TABLE to update statistics

   ANALYZE TABLE users;

Performance Example

Here's a real-world example showing the impact of indexes:

-- Without index: 1.2 seconds
SELECT * FROM orders WHERE customer_id = 12345;

-- Create index
CREATE INDEX idx_customer_id ON orders (customer_id);

-- With index: 0.003 seconds (400x faster!)
SELECT * FROM orders WHERE customer_id = 12345;

Conclusion

Indexes are essential for database performance, but they require thoughtful implementation. Start by indexing columns used in WHERE clauses, JOINs, and ORDER BY statements. Use EXPLAIN to verify your indexes are being used, and regularly review and remove unused indexes.

Remember: the goal isn't to index everything, but to index strategically based on your actual query patterns.

Quick Reference Cheat Sheet

-- Basic index
CREATE INDEX idx_name ON table (column);

-- Composite index
CREATE INDEX idx_name ON table (col1, col2);

-- Unique index
CREATE UNIQUE INDEX idx_name ON table (column);

-- Full-text index
CREATE FULLTEXT INDEX idx_name ON table (column);

-- Prefix index
CREATE INDEX idx_name ON table (column(10));

-- View indexes
SHOW INDEX FROM table;

-- Drop index
DROP INDEX idx_name ON table;

-- Check query plan
EXPLAIN SELECT * FROM table WHERE column = value;

Happy indexing! 🚀

---

Have questions about MySQL indexes? Drop them in the comments below!