MySQL Index Management Cheatsheet

This cheatsheet covers the basics of MySQL index management, including creating, viewing, and dropping indexes, as well as best practices and performance considerations.

Creating Indexes
- Single-Column Index
- Multi-Column Index
- Unique Index
- Fulltext Index
- Prefix Index
Viewing Indexes
Dropping Indexes
Modifying Indexes
Index Types
Best Practices and Performance Considerations
Invisible Indexes

Creating Indexes

Single-Column Index

-- Basic syntax
CREATE INDEX index_name ON table_name (column_name);

-- Example
CREATE INDEX idx_last_name ON employees (last_name);

Multi-Column Index

-- Basic syntax
CREATE INDEX index_name ON table_name (column1, column2, ...);

-- Example
CREATE INDEX idx_name ON employees (last_name, first_name);

Unique Index

-- Basic syntax
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- Example
CREATE UNIQUE INDEX idx_email ON users (email);

Fulltext Index

-- Basic syntax
CREATE FULLTEXT INDEX index_name ON table_name (column_name);

-- Example
CREATE FULLTEXT INDEX idx_description ON products (description);

Prefix Index

For long string columns, you can index just the first few characters:

CREATE INDEX index_name ON table_name (column_name(length));

-- Example
CREATE INDEX idx_title ON articles (title(50));

Viewing Indexes

Show Indexes on a Table

SHOW INDEX FROM table_name;

-- Example
SHOW INDEX FROM employees;

View Index Information from Information Schema

SELECT index_name, column_name, non_unique
FROM information_schema.statistics
WHERE table_schema = 'database_name' AND table_name = 'table_name';

Dropping Indexes

Drop Index

-- Basic syntax
DROP INDEX index_name ON table_name;

-- Example
DROP INDEX idx_last_name ON employees;

Drop Primary Key

ALTER TABLE table_name DROP PRIMARY KEY;

Modifying Indexes

MySQL doesn't support directly modifying an existing index. Instead, you need to drop the existing index and create a new one.

-- Drop the existing index
DROP INDEX old_index_name ON table_name;

-- Create the new index
CREATE INDEX new_index_name ON table_name (column1, column2);

Index Types

B-Tree Index (Default)

The default index type, suitable for most scenarios.

Hash Index

Available for MEMORY tables, good for equality comparisons.

R-Tree Index

Used for spatial data types.

Best Practices and Performance Considerations

Choose the right columns: Index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.
Consider selectivity: Indexes work best on columns with high selectivity (many unique values).
Avoid over-indexing: Too many indexes can slow down INSERT, UPDATE, and DELETE operations.
Use EXPLAIN: Use the EXPLAIN statement to analyze query execution plans and index usage.
```
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
```
Monitor index usage: Use the sys schema to identify unused indexes.
```
SELECT * FROM sys.schema_unused_indexes;
```
Update statistics: Ensure your index statistics are up to date for optimal query planning.
```
ANALYZE TABLE table_name;
```
Consider covering indexes: Include all columns from a query in an index to allow index-only scans.
```
CREATE INDEX idx_covering ON employees (last_name, first_name, salary);
```
Use composite indexes wisely: Place the most selective column first in a composite index.
Avoid function-based conditions: Conditions like WHERE YEAR(date_column) = 2023 can prevent index usage.
Consider partitioning: For very large tables, partitioning can improve query performance alongside proper indexing.

Invisible Indexes

MySQL 8.0+ supports invisible indexes, which are maintained but not used by the optimizer.

-- Create an invisible index
CREATE INDEX idx_invisible ON table_name (column_name) INVISIBLE;

-- Make an existing index invisible
ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

-- Make an invisible index visible
ALTER TABLE table_name ALTER INDEX index_name VISIBLE;

📚Cheatsheets