Creating indexes in MySQL - Mechanics & Internals

Overview - Creating indexes

What is it?

Creating indexes means making special lists that help a database find data faster. Think of an index like the index in a book that tells you where to find a topic quickly. Without indexes, the database has to look through every row to find what you want, which takes more time. Indexes speed up searching, sorting, and filtering data in tables.

Why it matters

Without indexes, databases would be slow when searching for information, especially in big tables. This would make websites and apps feel sluggish or unresponsive. Indexes solve this by letting the database jump directly to the data you need, saving time and resources. They are essential for good performance and user experience.

Where it fits

Before learning about creating indexes, you should understand basic database tables and how queries work. After indexes, you can learn about query optimization, database design, and advanced indexing techniques like full-text or spatial indexes.

Mental Model

Core Idea

An index is a special data structure that lets the database find rows quickly without scanning the whole table.

Think of it like...

Creating an index is like making a detailed table of contents for a book, so you don’t have to flip through every page to find a chapter.

Table: Users
┌─────────────┬───────────────┐
│ id (PK)    │ name          │
├─────────────┼───────────────┤
│ 1          │ Alice         │
│ 2          │ Bob           │
│ 3          │ Charlie       │
└─────────────┴───────────────┘

Index on 'name':
┌─────────────┬───────────────┐
│ name       │ id            │
├─────────────┼───────────────┤
│ Alice      │ 1             │
│ Bob        │ 2             │
│ Charlie    │ 3             │
└─────────────┴───────────────┘

Build-Up - 7 Steps

1

Foundation

What is an index in databases

Concept: Introduce the basic idea of an index as a tool to speed up data retrieval.

An index is like a shortcut for the database. Instead of looking at every row in a table to find data, the database uses the index to jump directly to the right place. This makes searching much faster, especially when tables have many rows.

Result

You understand that indexes help databases find data quickly without scanning the entire table.

Understanding that indexes are shortcuts helps you see why they are crucial for performance.

2

Foundation

Types of indexes in MySQL

Concept: Learn about common index types: primary key, unique, and regular indexes.

MySQL supports several index types: - Primary Key: Uniquely identifies each row. Automatically indexed. - Unique Index: Ensures no duplicate values in a column. - Regular Index: Speeds up searches but allows duplicates. Each type serves different purposes but all help speed up queries.

Result

You can recognize different index types and their roles in data integrity and speed.

Knowing index types helps you choose the right one for your data and queries.

3

Intermediate

How to create an index in MySQL

Concept: Learn the syntax and process to create indexes on table columns.

You create an index using the CREATE INDEX statement or when creating a table. For example: CREATE INDEX idx_name ON users(name); This creates an index named 'idx_name' on the 'name' column of the 'users' table. The database then builds the index to speed up searches on that column.

Result

You can write SQL commands to add indexes to existing tables.

Knowing how to create indexes lets you improve query speed on important columns.

4

Intermediate

Choosing columns to index wisely

🤔Before reading on: Do you think indexing every column always makes queries faster? Commit to your answer.

Concept: Understand that not all columns benefit from indexing and some can slow down writes.

Indexes speed up reads but slow down writes like INSERT, UPDATE, and DELETE because the index must be updated too. Columns used often in WHERE clauses or JOINs are good candidates. Columns with many repeated values or rarely searched may not benefit from indexes.

Result

You learn to balance indexing for read speed without hurting write performance.

Knowing when to index prevents wasted resources and keeps your database efficient.

5

Intermediate

Composite indexes for multiple columns

🤔Before reading on: Do you think a composite index on (A, B) can speed up queries filtering only by B? Commit to your answer.

Concept: Learn about indexes that cover multiple columns and how their order matters.

A composite index is an index on two or more columns, like: CREATE INDEX idx_name_age ON users(name, age); This index helps queries filtering by name or by both name and age, but not by age alone. The order of columns in the index is important for how it is used.

Result

You understand how to create and use composite indexes effectively.

Knowing the order and use of composite indexes helps optimize complex queries.

6

Advanced

Impact of indexes on database performance

🤔Before reading on: Do you think adding more indexes always improves overall database speed? Commit to your answer.

Concept: Explore the trade-offs between faster reads and slower writes caused by indexes.

Indexes speed up SELECT queries but add overhead to INSERT, UPDATE, and DELETE because indexes must be maintained. Too many indexes can slow down data changes and increase storage. Monitoring and tuning indexes is important for balanced performance.

Result

You see the real-world impact of indexes on different database operations.

Understanding trade-offs helps you design indexes that fit your application's needs.

7

Expert

How MySQL stores and uses indexes internally

🤔Before reading on: Do you think MySQL uses the same data structure for all index types? Commit to your answer.

Concept: Learn about the internal data structures like B-trees and how MySQL uses them for indexes.

MySQL commonly uses B-tree structures for indexes, which keep data sorted and balanced for fast searching. Primary keys and unique indexes use B-trees. Some storage engines support other types like hash indexes. Understanding this helps explain why order matters and how range queries work.

Result

You gain insight into the internal mechanics of index storage and usage.

Knowing internal structures clarifies why some queries are fast and others are not.

Under the Hood

When you create an index, MySQL builds a B-tree data structure that stores the indexed column values in sorted order along with pointers to the corresponding rows. When you run a query with a condition on the indexed column, MySQL searches the B-tree instead of scanning the whole table. This reduces the number of rows it checks, speeding up data retrieval.

Why designed this way?

B-trees were chosen because they keep data balanced and sorted, allowing fast searches, inserts, and deletes. This structure works well on disk and in memory, minimizing the number of disk reads. Alternatives like hash indexes exist but have limitations, so B-trees are the default for general-purpose indexing.

Table 'users'
┌─────────────┬───────────────┐
│ id          │ name          │
├─────────────┼───────────────┤
│ 1           │ Alice         │
│ 2           │ Bob           │
│ 3           │ Charlie       │
└─────────────┴───────────────┘

B-tree index on 'name':
          ┌─────────────┐
          │    Bob      │
          ├─────┬───────┤
      ┌───┘     │       └───┐
  ┌───┴───┐ ┌───┴───┐ ┌─────┴────┐
│ Alice │ │ Bob   │ │ Charlie  │
│ id=1  │ │ id=2  │ │ id=3     │
└───────┘ └───────┘ └──────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does adding an index always make all queries faster? Commit yes or no.

Common Belief: Adding an index always speeds up every query on the table.

Reality: Indexes speed up queries that use the indexed columns but can slow down data changes like inserts and updates because the index must be updated too.

Why it matters: Blindly adding indexes can degrade overall database performance and increase storage costs.

Quick: Can a composite index on (A, B) speed up queries filtering only by B? Commit yes or no.

Common Belief: A composite index on (A, B) helps queries filtering only by B.

Reality: A composite index on (A, B) can only speed up queries filtering by A or by both A and B, not by B alone.

Why it matters: Misunderstanding this leads to ineffective indexes and slow queries.

Quick: Is a primary key index the same as a unique index? Commit yes or no.

Common Belief: Primary key and unique indexes are the same and interchangeable.

Reality: Primary keys uniquely identify rows and cannot be null; unique indexes enforce uniqueness but can allow nulls depending on the database.

Why it matters: Confusing these can cause design errors and data integrity issues.

Quick: Does indexing a column with many repeated values always improve performance? Commit yes or no.

Common Belief: Indexing any column improves performance regardless of data distribution.

Reality: Indexing columns with low uniqueness (many repeated values) often does not improve performance and can waste resources.

Why it matters: Wasting indexes on low-cardinality columns can slow down writes and increase storage without benefits.

Expert Zone

1

Composite indexes are most effective when queries filter on the leftmost columns; ignoring this leads to unused indexes.

2

Covering indexes that include all columns needed by a query can eliminate the need to read the table rows, greatly speeding up queries.

3

Index fragmentation over time can degrade performance, so regular maintenance like OPTIMIZE TABLE is important.

When NOT to use

Avoid creating indexes on columns that are rarely used in queries or have very low uniqueness. Instead, consider full table scans or other optimization techniques like caching. For very large text or JSON columns, specialized indexes or search engines may be better.

Production Patterns

In production, indexes are carefully chosen based on query patterns and monitored with tools like EXPLAIN. Composite and covering indexes are common. Indexes are added incrementally and tested to balance read and write performance.

Connections

Data Structures

Indexes use data structures like B-trees to organize data efficiently.

Understanding B-trees from computer science helps grasp how indexes speed up searches.

Caching

Indexes reduce the need for full data scans, similar to how caching stores frequently accessed data for quick retrieval.

Knowing caching principles clarifies why indexes improve performance by reducing work.

Library Cataloging

Indexes in databases are like library card catalogs that help find books quickly without searching every shelf.

Recognizing this real-world system shows why indexes are essential for managing large collections efficiently.

Common Pitfalls

#1 Creating indexes on every column without considering query patterns.

Wrong approach:

CREATE INDEX idx_all ON users(id, name, age, email, address);

Correct approach:

CREATE INDEX idx_name ON users(name);

Root cause:

Misunderstanding that more indexes always mean faster queries, ignoring write overhead and storage costs.

#2 Creating a composite index with columns in the wrong order.

Wrong approach:

CREATE INDEX idx_age_name ON users(age, name); -- but queries filter mostly by name

Correct approach:

CREATE INDEX idx_name_age ON users(name, age);

Root cause:

Not knowing that composite index order affects which queries can use the index.

#3 Assuming a unique index allows NULL values like a primary key.

Wrong approach:

ALTER TABLE users ADD UNIQUE (email); -- expecting no NULLs allowed

Correct approach:

ALTER TABLE users ADD PRIMARY KEY (email); -- enforces no NULLs and uniqueness

Root cause:

Confusing unique constraints with primary keys and their NULL handling.

Key Takeaways

Indexes are special data structures that speed up data retrieval by avoiding full table scans.

Not all columns should be indexed; choose columns based on query patterns and data uniqueness.

Composite indexes speed up queries on multiple columns but the order of columns matters.

Indexes improve read performance but add overhead to write operations, so balance is key.

Understanding how indexes work internally helps design better databases and optimize queries.