MySQL - why not index every field?
Master System Design with Codemia
Enhance your system design skills with over 120 practice problems, detailed solutions, and hands-on exercises.
MySQL is a widely-used open-source relational database management system (RDBMS) that establishes a robust platform for storing, retrieving, and managing data. Indexing is a technique employed in MySQL to optimize the speed of data retrieval operations. While the intuitive solution might seem to be indexing every field in a table to ensure maximum performance, this approach often leads to inefficiencies and increased complexities.
What is an Index in MySQL?
An index in MySQL is a data structure that enhances the speed of data retrieval operations on a table at the cost of additional writes and increased storage. MySQL uses several types of indexes, such as:
- Primary Index: Automatically created for primary key columns.
- Secondary Index: Created on non-primary key columns.
- Unique Index: Ensures all values in a column are distinct.
- Composite Index: Index made on multiple columns.
- Full-text Index: Supports full-text searches.
Indexes are generally implemented using B-trees or hash tables. B-trees allow for logarithmic search times, improving query efficiency.
Why Not Index Every Field?
1. Increased Write Overhead
When a new record is inserted, updated, or deleted, all the indexes in the table need to be updated. Imagine a table with ten fields, each having its own index. A single INSERT operation will require updates to all ten indexes, which can significantly slow down write performance.
Example:
- High Cardinality Fields: Columns with a high number of unique values, like UUIDs or timestamps, are excellent candidates.
- Frequently Queried Columns: If a column is part of WHERE clauses, ORDER BY clauses, or joins, indexing it can offer performance improvements.
- Primary and Foreign Keys: These are often indexed by default due to their importance in the relational database model.
- Indexing `Department` if frequent lookups are based on department.
- Indexing `JoinDate` for range queries like finding employees who joined within certain dates.
- Avoiding indexes on `Name` and `Salary` unless specific query patterns justify them.
