Cheatsheet collection for go, rust, python, shell and javascript.

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.

  1. Creating Indexes
    • Single-Column Index
    • Multi-Column Index
    • Unique Index
    • Fulltext Index
    • Prefix Index
  2. Viewing Indexes
  3. Dropping Indexes
  4. Modifying Indexes
  5. Index Types
  6. Best Practices and Performance Considerations
  7. 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


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

  1. Choose the right columns: Index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses.

  2. Consider selectivity: Indexes work best on columns with high selectivity (many unique values).

  3. Avoid over-indexing: Too many indexes can slow down INSERT, UPDATE, and DELETE operations.

  4. Use EXPLAIN: Use the EXPLAIN statement to analyze query execution plans and index usage.

    EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
  5. Monitor index usage: Use the sys schema to identify unused indexes.

    SELECT * FROM sys.schema_unused_indexes;
  6. Update statistics: Ensure your index statistics are up to date for optimal query planning.

    ANALYZE TABLE table_name;
  7. 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);
  8. Use composite indexes wisely: Place the most selective column first in a composite index.

  9. Avoid function-based conditions: Conditions like WHERE YEAR(date_column) = 2023 can prevent index usage.

  10. 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

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