MYSQL Tutorial

MySQL Indexing

Indexes speed up data retrieval by allowing MySQL to find rows quickly without scanning the entire table. They trade a bit of storage and write performance for much faster SELECT queries.

Types of Indexes

• PRIMARY KEY: Unique clustered index on primary key column(s).
• UNIQUE: Ensures uniqueness and speeds lookups.
• INDEX (a.k.a. KEY): Standard non-unique index.
• COMPOSITE: Index on multiple columns for combined lookups.
• FULLTEXT: Optimized for text searches (MyISAM/InnoDB 5.6+).
• SPATIAL: For geographic data types (MyISAM/InnoDB 5.7+).

Creating Indexes

-- Single-column index
CREATE INDEX idx_lastname
  ON employees(last_name);

-- Composite index
CREATE INDEX idx_dept_salary
  ON employees(department_id, salary);

-- Fulltext index (for text search)
CREATE FULLTEXT INDEX ft_idx_content
  ON articles(content);
  

Dropping Indexes

-- Drop by name
DROP INDEX idx_lastname ON employees;

-- For fulltext/spatial, same syntax
DROP INDEX ft_idx_content ON articles;
  

When to Index

• Columns used frequently in WHERE, JOIN, ORDER BY or GROUP BY.
• Columns with high selectivity (many distinct values).
• Foreign key columns for faster lookups.

Tips & Best Practices

• Avoid over-indexing: each index slows INSERT/UPDATE/DELETE.
• Use EXPLAIN to see if MySQL uses your index for a query.
• Keep composite index column order matching query filters (leftmost prefix).
• Regularly ANALYZE TABLE and OPTIMIZE TABLE to update statistics.
• Consider covering indexes that include all columns used by a query to avoid lookups.