MySQL & Database Design

Creating and Managing Indexes

13 min Lesson 15 of 40

Creating and Managing Indexes

Now that you understand how indexes work, it's time to learn the practical skills of creating, modifying, and managing indexes in MySQL. In this lesson, we'll cover various syntax options, best practices, and advanced techniques.

CREATE INDEX Syntax

There are multiple ways to create indexes in MySQL. Let's explore each method:

Method 1: CREATE INDEX Statement

-- Single column index
CREATE INDEX idx_email ON users(email);

-- Multi-column (composite) index
CREATE INDEX idx_name ON users(first_name, last_name);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

Method 2: ALTER TABLE Statement

-- Add single index
ALTER TABLE users ADD INDEX idx_country (country);

-- Add composite index
ALTER TABLE users ADD INDEX idx_location (country, city, zip_code);

-- Add unique index
ALTER TABLE users ADD UNIQUE KEY idx_email (email);

-- Add full-text index
ALTER TABLE articles ADD FULLTEXT idx_content (title, content);

Method 3: CREATE TABLE with Index Definition

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(255),
    country VARCHAR(50),
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Define indexes during table creation
    INDEX idx_email (email),
    INDEX idx_location (country, city),
    UNIQUE KEY idx_username (username)
);

Best Practice: Use CREATE INDEX for existing tables and include index definitions in CREATE TABLE for new tables. This makes your table structure self-documenting.

Index Naming Conventions

Following consistent naming conventions makes index management easier:

Recommended Naming Patterns:

-- Single column indexes
idx_{column_name}
Example: idx_email, idx_country

-- Composite indexes
idx_{column1}_{column2}
Example: idx_country_city, idx_category_published

-- Unique indexes
uk_{column_name} or uniq_{column_name}
Example: uk_email, uniq_username

-- Full-text indexes
ft_{column_name} or fulltext_{column_name}
Example: ft_content, fulltext_title_body

-- Foreign key indexes
fk_{table}_{column}
Example: fk_orders_user_id

Why Naming Matters: Good names help you understand what each index does without looking at the table structure. This is especially important in large databases with hundreds of indexes.

Composite Index Column Order

The order of columns in a composite index is critical for performance. Consider these guidelines:

-- Scenario: You have these common queries
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC;

-- GOOD: customer_id first (most selective and used in all queries)
CREATE INDEX idx_customer_status_date
ON orders(customer_id, status, created_at);

-- This index can be used for:
✓ WHERE customer_id = 123
✓ WHERE customer_id = 123 AND status = 'pending'
✓ WHERE customer_id = 123 AND status = 'pending' ORDER BY created_at
✓ WHERE customer_id = 123 ORDER BY created_at (partial use)

-- BAD: Wrong column order
CREATE INDEX idx_bad ON orders(status, created_at, customer_id);
-- Can't efficiently use this for WHERE customer_id = 123

Column Order Rules:

1. Equality First: Columns used with = or IN
2. Range Second: Columns used with <, >, BETWEEN
3. Sorting Last: Columns used in ORDER BY

Example:
WHERE country = 'USA'      -- Equality (first)
  AND age > 18            -- Range (second)
ORDER BY created_at DESC   -- Sorting (last)

Optimal index:
CREATE INDEX idx_optimal ON users(country, age, created_at);

Covering Indexes

A covering index contains all columns needed by a query, eliminating the need to access the table data:

-- Query that needs id, email, and country
SELECT id, email, country FROM users WHERE email = 'john@example.com';

-- Regular index on email
CREATE INDEX idx_email ON users(email);
-- MySQL: Find row in index → Access table to get id and country

-- Covering index includes all needed columns
CREATE INDEX idx_email_covering ON users(email, id, country);
-- MySQL: Find row in index → All data in index (no table access needed!)

Result: Faster query (no table lookup required)

Check if your query uses a covering index with EXPLAIN:

EXPLAIN SELECT id, email, country
FROM users
WHERE email = 'john@example.com';

+----+------+-------------------+-------+-------------+
| id | type | key               | Extra                |
+----+------+-------------------+-------+-------------+
| 1  | ref  | idx_email_covering| Using index          |
+----+------+-------------------+-------+-------------+

"Using index" = Covering index is being used! ✓

Tip: Covering indexes are extremely powerful for read-heavy applications, but be careful not to make them too large. Balance coverage with index size.

Invisible Indexes (MySQL 8.0+)

MySQL 8.0 introduced invisible indexes - indexes that exist but are not used by the query optimizer:

-- Create invisible index
CREATE INDEX idx_email ON users(email) INVISIBLE;

-- Make existing index invisible
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

-- Make invisible index visible again
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

Why Use Invisible Indexes?

Use Case 1: Testing Index Removal
-- Before dropping an index, make it invisible to test impact
ALTER TABLE users ALTER INDEX idx_country INVISIBLE;
-- Monitor application performance for 24 hours
-- If no issues, drop the index safely
DROP INDEX idx_country ON users;

Use Case 2: Conditional Index Usage
-- Create index for future use but don't use it yet
CREATE INDEX idx_future ON users(new_column) INVISIBLE;
-- Enable when needed
ALTER TABLE users ALTER INDEX idx_future VISIBLE;

Dropping Indexes

Remove indexes that are no longer needed to reduce overhead:

-- Method 1: DROP INDEX
DROP INDEX idx_country ON users;

-- Method 2: ALTER TABLE
ALTER TABLE users DROP INDEX idx_country;

-- Drop multiple indexes at once
ALTER TABLE users
    DROP INDEX idx_country,
    DROP INDEX idx_city,
    DROP INDEX idx_zip;

Warning: You cannot drop a PRIMARY KEY using DROP INDEX. Use ALTER TABLE to drop and recreate primary keys:

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (id);

Modifying Existing Indexes

MySQL doesn't support direct index modification. You must drop and recreate:

-- Want to change idx_name from (first_name) to (first_name, last_name)

-- Step 1: Drop old index
DROP INDEX idx_name ON users;

-- Step 2: Create new index
CREATE INDEX idx_name ON users(first_name, last_name);

-- Or do both in one statement
ALTER TABLE users
    DROP INDEX idx_name,
    ADD INDEX idx_name (first_name, last_name);

Index Length Prefix

For VARCHAR and TEXT columns, you can index only the first N characters to save space:

-- Index first 10 characters of email
CREATE INDEX idx_email ON users(email(10));

-- Useful for long TEXT columns
CREATE INDEX idx_description ON products(description(50));

-- Composite index with prefix
CREATE INDEX idx_name ON users(first_name(5), last_name(10));

When to use prefix indexes:

✓ Use prefix when:
- Column is very long (TEXT, BLOB)
- First N characters are unique enough
- Index size is a concern

✗ Avoid prefix when:
- You need exact matching
- You use ORDER BY on the column (prefix indexes can't be used for sorting)
- Column is already short (VARCHAR(50) or less)

Real-World Index Management Scenario

Let's optimize a real e-commerce orders table:

-- Original table (no indexes except PRIMARY KEY)
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Common queries:
-- Q1: Find customer's orders
SELECT * FROM orders WHERE customer_id = 123;

-- Q2: Find pending orders
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

-- Q3: Find customer's recent orders
SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 10;

-- Q4: Admin dashboard - today's revenue
SELECT SUM(total_amount) FROM orders
WHERE created_at >= CURDATE() AND status != 'cancelled';

Optimal indexes for these queries:

-- Index 1: For Q1 and Q3 (customer queries)
CREATE INDEX idx_customer_created
ON orders(customer_id, created_at);
-- Helps with customer filter + date sorting

-- Index 2: For Q2 (status queries)
CREATE INDEX idx_status_created
ON orders(status, created_at);
-- Helps with status filter + date sorting

-- Index 3: For Q4 (reporting queries with covering index)
CREATE INDEX idx_reporting
ON orders(created_at, status, total_amount);
-- Covering index: has all columns needed (no table access)

-- Verify our indexes
SHOW INDEXES FROM orders;

Practice Exercise:

Scenario: You have a social media posts table with poor performance.

CREATE TABLE posts (
    id INT PRIMARY KEY,
    user_id INT,
    content TEXT,
    category VARCHAR(50),
    likes_count INT DEFAULT 0,
    created_at TIMESTAMP,
    is_published BOOLEAN DEFAULT 1
);

-- These queries are slow:
-- Q1: User's published posts
SELECT * FROM posts
WHERE user_id = 456 AND is_published = 1
ORDER BY created_at DESC;

-- Q2: Top posts by category
SELECT id, category, likes_count FROM posts
WHERE category = 'Technology' AND is_published = 1
ORDER BY likes_count DESC
LIMIT 10;

-- Q3: Recent posts with content preview
SELECT id, LEFT(content, 100) as preview, created_at
FROM posts
WHERE is_published = 1
ORDER BY created_at DESC
LIMIT 20;

Task: Create optimal indexes for these queries.

Solution:

-- Index for Q1 (user's posts)
CREATE INDEX idx_user_published_created
ON posts(user_id, is_published, created_at);
-- Covers user filter, published filter, and date sorting

-- Index for Q2 (top posts - covering index)
CREATE INDEX idx_category_published_likes
ON posts(category, is_published, likes_count, id);
-- Covering index includes all needed columns (no table access)

-- Index for Q3 (recent posts)
CREATE INDEX idx_published_created
ON posts(is_published, created_at);
-- Can't include content (too large) but optimizes the filter+sort
-- Note: Still needs table access for content, but much faster

-- Verify performance improvement
EXPLAIN SELECT * FROM posts
WHERE user_id = 456 AND is_published = 1
ORDER BY created_at DESC;
-- Should show: type=ref, key=idx_user_published_created

Best Practices Summary

✓ Use meaningful, consistent index names
✓ Create composite indexes for multi-column queries
✓ Put most selective columns first in composite indexes
✓ Use covering indexes for frequently-run queries
✓ Test index changes on a copy of production data
✓ Monitor index usage with EXPLAIN and slow query log
✓ Remove unused indexes (check with sys.schema_unused_indexes)

✗ Don't create redundant indexes (idx_a, idx_a_b when idx_a_b exists)
✗ Don't over-index (each index adds overhead to writes)
✗ Don't index every column "just in case"
✗ Don't forget to test performance before and after

Summary

In this lesson, you learned:

Multiple ways to create indexes: CREATE INDEX, ALTER TABLE, and inline in CREATE TABLE
Index naming conventions for better organization
Composite index column ordering rules (equality → range → sorting)
Covering indexes eliminate table access for better performance
Invisible indexes (MySQL 8.0+) for safe index testing
How to drop and modify indexes
Index prefix technique for long columns
Real-world index optimization strategies

Next Up: In the next lesson, we'll master query optimization using EXPLAIN and learn how to identify and fix slow queries!