MySQL: MySQL Index Basics: Creation and Management

Last updated: 2026-08-26

Indexes are key to optimizing database performance—a query without an index is like looking up a word in a dictionary without consulting the table of contents.

This lesson explains the principles behind indexes, as well as how to create and manage them.

1. What You'll Learn

• What is an index, and why do we need indexes?
• Principles of the B+Tree Index Structure
• CREATE INDEX / ALTER TABLE: Create an index
• View and delete indexes
• Advantages and Disadvantages of Indexes

2. Real-Life Scenarios

(1) Pain Point: Queries are too slow

1 million user records, search by email address:

SELECT * FROM users WHERE email = 'alice@email.com';
-- Execution time: 2.5 seconds (Full Table Scan)

(2) Solutions for Indexes

CREATE INDEX idx_email ON users(email);
-- Search again: 0.003 seconds (Index Lookup)

3. How Indexes Work

(1) B+Tree Structure

graph TB
    R[Root Node<br/>10 | 30 | 50] --> L1[1-10]
    R --> L2[11-30]
    R --> L3[31-50]
    R --> L4[51+]
    L1 --> D1[Data: 1,3,5,7,9]
    L1 --> D2[Data: 2,4,6,8,10]
    L2 --> D3[Data: 11,15,20]
    L2 --> D4[Data: 25,28,30]
    L3 --> D5[Data: 31,35,40]
    L3 --> D6[Data: 45,48,50]

graph TB
    R[Root Node<br/>10 | 30 | 50] --> L1[1-10]
    R --> L2[11-30]
    R --> L3[31-50]
    R --> L4[51+]
    L1 --> D1[Data: 1,3,5,7,9]
    L1 --> D2[Data: 2,4,6,8,10]
    L2 --> D3[Data: 11,15,20]
    L2 --> D4[Data: 25,28,30]
    L3 --> D5[Data: 31,35,40]
    L3 --> D6[Data: 45,48,50]

(2) Index Types

4. Creating Indexes

▶ Example: Creating an Index

Output:

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

-- Methods1：CREATE INDEX
CREATE INDEX idx_email ON users(email);

-- Unique Index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Methods2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_username (username);

-- Composite Index（Composite Index）
CREATE INDEX idx_name_email ON users(username, email);

-- Prefix Index
CREATE INDEX idx_email_prefix ON users(email(10));

Output:

Output displayed

5. View the Index

▶ Example: Viewing Index Information

-- View all indexes on the table
SHOW INDEX FROM users;

-- View Index Information
SHOW INDEX FROM users\G

Output:

+-------+------------+----------+--------------+-------------+
| Table | Key_name   | Seq_in_index | Column_name | Index_type |
+-------+------------+--------------+-------------+------------+
| users | PRIMARY    |            1 | id          | BTREE      |
| users | idx_email  |            1 | email       | BTREE      |
+-------+------------+--------------+-------------+------------+

6. Deleting an Index

▶ Example: Deleting an Index

-- Methods1：DROP INDEX
DROP INDEX idx_email ON users;

-- Methods2：ALTER TABLE
ALTER TABLE users DROP INDEX idx_email;

-- Delete Primary Key
ALTER TABLE users DROP PRIMARY KEY;

Output:

Output displayed

7. Use Cases for Indexes

❓ FAQ

📖 Summary

• Indexes speed up queries but reduce write performance
• B+Tree is the default index structure, supporting exact matches, range queries, and sorted queries.
• CREATE INDEX to create, DROP INDEX to delete
• Applicable: WHERE/JOIN/ORDER BY/GROUP BY fields
• Not applicable: Frequent updates, low selectivity, small tables

📝 Exercises

1. Basic Question (Difficulty ⭐): Create a unique index on the email field of the users table.

2. Advanced Problem (Difficulty ⭐⭐): Create a composite index (department, salary) and verify the leftmost prefix principle.

3. Challenge Question (Difficulty: ⭐⭐⭐): Use EXPLAIN to compare the performance differences between queries with and without indexes.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us