Indexing Optimization

Indexing is one of the most powerful techniques for improving the performance of MySQL queries, but it must be used wisely to get the best results. Well-optimized indexes can drastically speed up query execution by allowing MySQL to retrieve rows faster.

1. Types of Indexes in MySQL

MySQL provides various types of indexes, each with its specific use cases:

• Primary Key Index: Automatically created when you define a primary key on a column.
• Unique Index: Ensures all values in a column are unique.
• Regular (Non-Unique) Index: Standard index for improving query performance.
• Composite Index: Index on multiple columns.
• Full-Text Index: Specialized index for text search queries.

2. General Guidelines for Indexing

Index Columns in WHERE, JOIN, and ORDER BY Clauses

Columns that are frequently used in WHERE, JOIN, and ORDER BY clauses should be indexed. This reduces the number of rows MySQL has to scan, making query execution faster.

Example:

SELECT * FROM users WHERE email = 'example@example.com';

In this case, creating an index on the email column would improve query performance:

CREATE INDEX idx_users_email ON users(email);

3. Composite Indexes

A composite index is an index on multiple columns. This is particularly useful when a query filters by multiple columns in sequence.

Example:

If you frequently query by both last_name and first_name:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

Creating a composite index would improve performance:

CREATE INDEX idx_employees_name ON employees(last_name, first_name);

Guidelines for Composite Indexes:

• Order Matters: The order in which columns are indexed matters. For example, an index on (last_name, first_name) can optimize a query on last_name alone or both last_name and first_name, but it won’t optimize a query on first_name alone.
• Leftmost Prefix Rule: The query optimizer can use the index as long as it starts with the leftmost column(s). For example, an index on (A, B, C) can be used by queries filtering on (A), (A, B), or (A, B, C).

4. Avoid Over-Indexing

Too many indexes can harm performance, especially for write-heavy applications (INSERT, UPDATE, DELETE). Each index requires additional disk space and increases the time needed to update the table.

Best Practices:

• Index only the columns necessary to speed up frequently used queries.
• Remove unused indexes: Regularly check for unused indexes and drop them if they are not used in any queries.

Query to Find Unused Indexes:

SELECT * FROM sys.schema_unused_indexes;

5. Full-Text Indexes for Searching Text

For text-heavy search queries, use full-text indexes instead of regular indexes, as they are optimized for natural language searches.

Example:

CREATE FULLTEXT INDEX idx_description ON products(description);

Then use the MATCH function to perform full-text searches:

SELECT * FROM products WHERE MATCH(description) AGAINST('search term');

6. Avoid Functions on Indexed Columns

Using functions on indexed columns in the WHERE clause prevents MySQL from using the index.

Example:

SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

MySQL cannot use an index on hire_date because of the YEAR() function. Instead, rewrite the query:

SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

This allows MySQL to use an index on the hire_date column.