MySQL - Indexes

MySQL - Database Indexes

An index is a special data structure that a database uses to dramatically speed up data retrieval operations on a table. Think of it like the index at the back of a book: instead of scanning every page to find a topic, you look it up in the index and go directly to the correct page. Similarly, a database index allows the query engine to find rows without having to scan the entire table.

The Cost of Indexing

While indexes are crucial for read performance (SELECT queries), they come at a cost. Every time you INSERT, UPDATE, or DELETE a row, the database must also update every index that includes that row. This adds overhead to write operations. Therefore, you should index strategically, not just index every column.

Indexes are invisible to users but are a fundamental tool for the database engine to execute queries efficiently.

Types of MySQL Indexes

MySQL offers several types of indexes, each suited for different purposes. You can create indexes on a single column or on multiple columns (a composite index).

Primary Key

A PRIMARY KEY is a special type of index that uniquely identifies each record in a table. It enforces two rules: the indexed column(s) must contain unique values, and they cannot contain NULL values. A table can have only one primary key. It’s the most common and important index.

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Unique Index

A UNIQUE index ensures that all values in a column (or combination of values in multiple columns) are distinct. Unlike a primary key, you can have multiple unique indexes per table, and they can permit a single NULL value.

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    UNIQUE INDEX (username),
    UNIQUE INDEX (email)
);

Simple Index (Non-Unique)

This is the most basic index type. Its only purpose is to improve query performance. It allows duplicate values and NULLs. You would use this on columns that are frequently searched or sorted but don’t need to be unique, like a last_name column.

CREATE INDEX idx_lastname ON Customers (last_name);

Composite Index (Multi-Column)

A composite index is an index on two or more columns. The order of columns in the index definition is very important. A query can use the index if it filters on the leading column(s) of the index. For example, an index on (last_name, first_name) can be used for queries filtering on last_name alone, or on both last_name and first_name.

CREATE INDEX idx_name ON Customers (last_name, first_name);

Full-text Index

A FULLTEXT index is used for performing natural language searches against text data in CHAR, VARCHAR, or TEXT columns. Instead of simple equality checks, it allows you to search for words and phrases within the text using the MATCH() ... AGAINST() syntax.

CREATE TABLE Articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
);

-- Example query
SELECT * FROM Articles
WHERE MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

Descending Index (MySQL 8.0+)

Traditionally, indexes stored data in ascending order. Since MySQL 8.0, you can explicitly create an index that stores keys in descending order. This can improve performance for queries that need to sort in descending order, such as finding the most recent posts.

CREATE INDEX idx_created_desc ON Posts (created_at DESC);

How to Choose Which Columns to Index

Deciding what to index is key to database performance tuning. Here are some general guidelines:

• Primary Keys: Always have a primary key on your tables.
• Foreign Keys: Columns that act as foreign keys should almost always be indexed to speed up joins.
• WHERE Clauses: Index columns that are frequently used in WHERE clauses.
• ORDER BY and GROUP BY Clauses: Indexing columns used for sorting and grouping can eliminate slow filesort operations.
• Column Cardinality: Favor indexing columns with high cardinality (many unique values), like an email address, over columns with low cardinality (few unique values), like a gender field.

Analyzing Query Performance with EXPLAIN

To see if MySQL is using your indexes for a particular query, you can use the EXPLAIN command. Prepending EXPLAIN to a SELECT statement shows you the query execution plan.

EXPLAIN SELECT * FROM Customers WHERE last_name = 'Smith';

Look at the output of EXPLAIN. The key column will show which index was used. If it’s NULL, no index was used, and the rows column will likely be high, indicating a full table scan. This is a powerful tool for diagnosing slow queries.