MySQL Create Index
Speed up your database queries with indexes
🚀 What is a MySQL Index?
An index in MySQL is a data structure that improves the speed of data retrieval operations on database tables. Indexes work like a book's index, helping MySQL find rows faster without scanning the entire table.
-- Create a simple index
CREATE INDEX idx_lastname
ON employees(last_name);
Result:
Index 'idx_lastname' created successfully on employees table
Types of Indexes
Single Column
Index on one column
CREATE INDEX idx_email
ON users(email);Composite
Index on multiple columns
CREATE INDEX idx_name
ON users(first_name, last_name);Unique
Ensures unique values
CREATE UNIQUE INDEX idx_username
ON users(username);Full-Text
For text searching
CREATE FULLTEXT INDEX idx_content
ON articles(content);🔹 Creating Indexes
You can create indexes using the CREATE INDEX statement. Indexes are automatically created for PRIMARY KEY and UNIQUE constraints. Choose columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY statements for better performance.
-- Create index on existing table
CREATE INDEX idx_city
ON customers(city);
-- Create index with table creation
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
INDEX idx_price (price)
);Result:
Query OK, 0 rows affected
Index created successfully
🔹 Viewing Indexes
To see all indexes on a table, use the SHOW INDEX command. This displays index names, column names, uniqueness, and other properties. Understanding existing indexes helps avoid creating duplicate indexes and optimizing query performance effectively.
-- Show all indexes on a table
SHOW INDEX FROM employees;
-- Alternative method
SHOW KEYS FROM employees;Result:
| Key_name | Column_name | Non_unique |
|---|---|---|
| PRIMARY | id | 0 |
| idx_lastname | last_name | 1 |
🔹 Dropping Indexes
Remove indexes that are no longer needed using the DROP INDEX statement. Unused indexes consume storage space and slow down INSERT, UPDATE, and DELETE operations. Regularly review and remove unnecessary indexes to maintain optimal database performance and reduce overhead.
-- Drop an index
DROP INDEX idx_lastname ON employees;
-- Alternative syntax
ALTER TABLE employees
DROP INDEX idx_lastname;Result:
Query OK, 0 rows affected
Index dropped successfully
🔹 Index Best Practices
Follow these guidelines for effective indexing: Index columns used in WHERE, JOIN, and ORDER BY clauses. Avoid over-indexing as it slows down write operations. Use composite indexes for queries filtering multiple columns. Monitor query performance and adjust indexes accordingly for optimal results.
Tips for Using Indexes:
- Index selective columns: Columns with many unique values
- Avoid indexing small tables: Full scan may be faster
- Consider composite indexes: For multi-column queries
- Monitor performance: Use EXPLAIN to analyze queries
- Limit index count: Too many indexes slow down writes
-- Check if index is used
EXPLAIN SELECT * FROM employees
WHERE last_name = 'Smith';