Eric Campbell 5 min read MySQL

How to Create Indexes in MySQL

An index in MySQL is a data structure that allows the database to find rows quickly without scanning the entire table. Think of it like a book's index—instead of reading every page to find mentions...

Key Insights

  • Indexes dramatically speed up SELECT queries but slow down INSERT, UPDATE, and DELETE operations—the right indexing strategy balances read and write performance based on your application’s access patterns.
  • Column order in composite indexes matters critically: MySQL can only use an index from left to right, so place the most selective columns first and consider your WHERE clause patterns.
  • Most applications suffer from either too few indexes (slow queries) or too many indexes (slow writes and wasted storage)—use EXPLAIN regularly to verify your indexes are actually being used.

Understanding MySQL Indexes

An index in MySQL is a data structure that allows the database to find rows quickly without scanning the entire table. Think of it like a book’s index—instead of reading every page to find mentions of “performance optimization,” you jump directly to the relevant pages.

Without indexes, MySQL performs table scans, reading every row sequentially. For a table with millions of rows, this becomes prohibitively expensive. A properly designed index can reduce query execution time from seconds to milliseconds.

The trade-off is straightforward: indexes consume disk space and slow down write operations. Every INSERT, UPDATE, or DELETE must update not just the table data but also all associated indexes. For read-heavy applications, this trade-off is worth it. For write-heavy workloads, you need to be more selective.

Creating Single-Column Indexes

The most basic index targets a single column. Here’s the fundamental syntax:

CREATE INDEX idx_email ON users(email);

This creates a B-Tree index (MySQL’s default) on the email column of the users table. The idx_ prefix is a naming convention that makes indexes easy to identify.

You can also add indexes to existing tables using ALTER TABLE:

ALTER TABLE users ADD INDEX idx_last_name (last_name);

For new tables, define indexes directly in the CREATE TABLE statement:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    last_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
);

This approach is cleaner and ensures indexes exist from the start. Single-column indexes work well for queries filtering on one field:

SELECT * FROM users WHERE email = 'user@example.com';
-- Uses idx_email efficiently

Composite Indexes and Column Order

Composite indexes span multiple columns and are essential for queries with multiple WHERE conditions or JOIN operations. Column order is critical—MySQL can only use an index from left to right.

CREATE INDEX idx_lastname_firstname ON users(last_name, first_name);

This index supports queries filtering on:

  • last_name alone
  • last_name AND first_name together

But it does NOT efficiently support queries filtering only on first_name. This is the left-prefix rule.

-- Uses idx_lastname_firstname efficiently
SELECT * FROM users WHERE last_name = 'Smith';

-- Uses idx_lastname_firstname efficiently  
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

-- Does NOT use idx_lastname_firstname (first_name isn't leftmost)
SELECT * FROM users WHERE first_name = 'John';

Verify this with EXPLAIN:

EXPLAIN SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

Look for type: ref and key: idx_lastname_firstname in the output—this confirms index usage.

For column ordering, follow these principles:

  1. Equality conditions first: Columns in WHERE clauses with = should come before range conditions (>, <, BETWEEN)
  2. High selectivity first: Columns with more unique values should come before less selective ones
  3. Match query patterns: Order columns based on how they appear in your most frequent queries
-- Good: Equality (status) before range (created_at)
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Bad: Range first limits index effectiveness
CREATE INDEX idx_created_status ON orders(created_at, status);

For the good index, this query uses the full index:

SELECT * FROM orders 
WHERE status = 'pending' 
AND created_at > '2024-01-01';

Index Types in MySQL

MySQL supports several index types beyond the default B-Tree:

UNIQUE indexes enforce uniqueness while providing fast lookups:

CREATE UNIQUE INDEX idx_username ON users(username);

-- Alternatively, during table creation
ALTER TABLE users ADD UNIQUE KEY idx_email (email);

PRIMARY KEY is a special UNIQUE index that doesn’t allow NULL values:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) UNIQUE,
    name VARCHAR(255)
);

FULLTEXT indexes enable natural language searching in text columns:

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT idx_content (content)
);

-- Search using MATCH AGAINST
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

FULLTEXT indexes work with CHAR, VARCHAR, and TEXT columns. They’re essential for implementing search functionality without external tools like Elasticsearch for smaller applications.

SPATIAL indexes support geographic data types:

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    coordinates POINT NOT NULL,
    SPATIAL INDEX idx_coordinates (coordinates)
);

These enable efficient queries for geographic proximity and boundaries.

Analyzing Index Performance

Creating indexes is only half the battle—you must verify they’re being used effectively.

EXPLAIN is your primary diagnostic tool:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com'\G

Key fields to examine:

  • type: Should be const, eq_ref, or ref for indexed lookups. ALL means a full table scan (bad).
  • possible_keys: Indexes MySQL considers using
  • key: The index actually used
  • rows: Estimated rows scanned (lower is better)

View all indexes on a table:

SHOW INDEX FROM users;

This displays index names, column names, cardinality (unique values), and index type.

Measure real performance impact:

-- Before index
SELECT * FROM orders WHERE status = 'pending';
-- Note the execution time

CREATE INDEX idx_status ON orders(status);

-- After index
SELECT * FROM orders WHERE status = 'pending';
-- Compare execution time

For InnoDB tables, check index statistics:

SELECT * FROM information_schema.INNODB_SYS_INDEXES 
WHERE table_id = (
    SELECT table_id FROM information_schema.INNODB_SYS_TABLES 
    WHERE name = 'database_name/table_name'
);

Best Practices and Common Pitfalls

Index columns used in WHERE, JOIN, and ORDER BY clauses. These are your high-value targets:

-- Frequently filtered
CREATE INDEX idx_status ON orders(status);

-- Frequently joined
CREATE INDEX idx_user_id ON orders(user_id);

-- Frequently sorted
CREATE INDEX idx_created_at ON orders(created_at);

Don’t over-index. Every index slows writes and consumes storage. A table with 10 indexes on 8 columns is probably over-indexed:

-- Too many indexes hurt performance
CREATE INDEX idx1 ON products(category);
CREATE INDEX idx2 ON products(price);
CREATE INDEX idx3 ON products(category, price);
CREATE INDEX idx4 ON products(price, category);
CREATE INDEX idx5 ON products(name);
-- ... and so on

Drop unused indexes. Identify candidates:

-- MySQL 5.7+
SELECT * FROM sys.schema_unused_indexes;

-- Manual cleanup
DROP INDEX idx_old_column ON users;

Avoid indexing low-cardinality columns like boolean flags or status fields with only 2-3 values. The exception is when combined with high-cardinality columns in composite indexes.

Monitor index cardinality. Low cardinality means the index isn’t selective:

SHOW INDEX FROM users WHERE Key_name = 'idx_status';
-- Check the Cardinality column

If cardinality is low relative to table size, the index may not be useful.

Use covering indexes for frequently accessed column combinations:

-- Query only needs id, email, last_name
CREATE INDEX idx_covering ON users(email, last_name, id);

-- This query can be satisfied entirely from the index
SELECT id, last_name FROM users WHERE email = 'test@example.com';

Rebuild fragmented indexes on tables with heavy UPDATE/DELETE activity:

OPTIMIZE TABLE users;

This reclaims space and improves index efficiency.

The key to effective indexing is understanding your query patterns. Start by indexing foreign keys and frequently filtered columns, then use EXPLAIN to identify slow queries and add targeted indexes. Measure the impact, and don’t be afraid to drop indexes that aren’t providing value. Indexing is an iterative process that evolves with your application.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.