MySQL Indexes

Indexes in MySQL are special data structures that improve the speed of data retrieval operations on database tables. They work like the index in a book, allowing the database to find data without scanning the entire table.

Why Use Indexes?

• Faster data retrieval
• Improved query performance
• Efficient sorting and grouping
• Enforce uniqueness constraints
• Speed up JOIN operations

Types of Indexes in MySQL

Creating Indexes

1. During Table Creation

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Define indexes
  INDEX idx_email (email),
  INDEX idx_created (created_at),
  
  -- Composite index
  INDEX idx_name_email (username, email)
);

2. After Table Creation

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

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

-- Create composite index
CREATE INDEX idx_name_dob ON users(first_name, birth_date);

-- Create fulltext index
CREATE FULLTEXT INDEX idx_content ON articles(content);

3. Add Index with ALTER TABLE

ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD UNIQUE INDEX idx_phone (phone_number);
ALTER TABLE users ADD FULLTEXT INDEX idx_bio (bio);

Managing Indexes

Show Indexes

-- Show all indexes on a table
SHOW INDEX FROM users;

-- Show create table statement (includes indexes)
SHOW CREATE TABLE users;

Drop Index

-- Drop an index
DROP INDEX idx_email ON users;

-- Using ALTER TABLE
ALTER TABLE users DROP INDEX idx_email;

Analyze Index Usage

-- Analyze table for index usage
ANALYZE TABLE users;

-- Check index statistics
SELECT * FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_NAME = 'users';

Index Best Practices

When to Create Indexes

• Primary key columns (automatic)
• Foreign key columns
• Columns frequently used in WHERE clauses
• Columns used for JOIN operations
• Columns used for ORDER BY and GROUP BY
• Columns with high selectivity (many unique values)

When NOT to Create Indexes

• Tables with few rows
• Columns rarely used in queries
• Columns frequently updated
• Columns with low selectivity (few unique values)
• TEXT/BLOB columns (except with prefix indexes)

Composite Indexes

Creating Composite Indexes

-- Good for queries filtering on multiple columns
CREATE INDEX idx_last_first ON employees(last_name, first_name);

-- This index helps with:
-- WHERE last_name = 'Smith'
-- WHERE last_name = 'Smith' AND first_name = 'John'
-- ORDER BY last_name, first_name

Composite Index Order Matters

-- Index (A, B, C) can be used for:
-- WHERE A = ?
-- WHERE A = ? AND B = ?
-- WHERE A = ? AND B = ? AND C = ?

-- But NOT for:
-- WHERE B = ?
-- WHERE C = ?
-- WHERE B = ? AND C = ?

Performance Analysis

Using EXPLAIN

-- Check query execution plan
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Key columns to check:
-- type: ALL (full scan) vs ref/range (index used)
-- key: Which index is used
-- rows: Estimated rows examined
-- Extra: Additional information

Slow Query Log

-- Enable slow query log in my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

-- Analyze slow queries
mysqldumpslow /var/log/mysql/slow.log

Common Index Issues

1. Too Many Indexes

Each index increases storage and slows down INSERT/UPDATE/DELETE

2. Missing Indexes

-- Queries doing full table scans
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- If customer_id has no index, it scans entire table

3. Unused Indexes

-- Check unused indexes
SELECT * FROM sys.schema_unused_indexes;

4. Index Fragmentation

-- Rebuild indexes to reduce fragmentation
OPTIMIZE TABLE table_name;

Index Types in Detail

B-Tree Index (Default)

• Most common index type
• Works with =, >, <, BETWEEN, LIKE 'prefix%'
• Supports sorting
• Good for high cardinality columns

Hash Index

• Memory tables only
• Very fast for exact matches
• Doesn't support range queries
• Not for sorting

Full-Text Index

-- Create fulltext index
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

-- Search using MATCH...AGAINST
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database tutorial');

Real-World Examples

E-commerce Database

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(200),
  category_id INT,
  price DECIMAL(10,2),
  created_at DATETIME,
  
  INDEX idx_category (category_id),
  INDEX idx_price (price),
  INDEX idx_created (created_at),
  FULLTEXT INDEX idx_name (name)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(20),
  
  INDEX idx_customer (customer_id),
  INDEX idx_date_status (order_date, status),
  INDEX idx_status (status)
);