Databases
MySQL
Indexing
Database Optimization
SQL Performance

MySQL - why not index every field?

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

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.

Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

All Rights Reserved.