Index maintenance in MySQL - Step-by-Step Execution

Concept Flow - Index maintenance

Create or Alter Table

↓

Add/Modify Index

↓

Index Structure Updated

↓

Data Insert/Update/Delete

↓

Index Entries Updated

↓

Query Uses Index for Faster Access

This flow shows how indexes are created or modified, then maintained automatically as data changes, helping queries run faster.

Execution Sample

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  dept_id INT,
  INDEX idx_dept (dept_id)
);

INSERT INTO employees VALUES (1, 'Alice', 10);

Creates a table with an index on dept_id, then inserts a row, updating the index.

Execution Table

Step	Action	Index State	Data State	Notes
1	Create table with index idx_dept on dept_id	Empty index structure created	No data	Index ready but no entries
2	Insert row (1, 'Alice', 10)	Index updated with dept_id=10 pointing to row 1	One row inserted	Index entry added for dept_id=10
3	Insert row (2, 'Bob', 20)	Index updated with dept_id=20 pointing to row 2	Two rows inserted	Index entry added for dept_id=20
4	Update row 1 dept_id to 30	Index entry for dept_id=10 removed, dept_id=30 added	Row 1 updated	Index reflects new dept_id
5	Delete row 2	Index entry for dept_id=20 removed	Row 2 deleted	Index entry removed
6	Query: SELECT * FROM employees WHERE dept_id=30	Index used to find row 1 quickly	Data accessed via index	Faster query due to index
7	End	Index consistent with data	Data consistent	Maintenance complete

💡 All data changes reflected in index; index ready for fast queries

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
Index entries	{}	{10: [row1]}	{10: [row1], 20: [row2]}	{30: [row1], 20: [row2]}	{30: [row1]}	{30: [row1]}
Table rows	{}	{1: ('Alice',10)}	{1: ('Alice',10), 2: ('Bob',20)}	{1: ('Alice',30), 2: ('Bob',20)}	{1: ('Alice',30)}	{1: ('Alice',30)}

Key Moments - 3 Insights

Why does the index entry for dept_id=10 disappear after step 4?

Because the dept_id value in row 1 changed from 10 to 30, the index must remove the old entry for 10 and add a new entry for 30, as shown in execution_table row 4.

Does deleting a row automatically remove its index entries?

Yes, when a row is deleted, all its index entries are removed to keep the index consistent, as seen in execution_table row 5.

How does the index help the query in step 6?

The index allows the database to quickly find rows with dept_id=30 without scanning the whole table, improving query speed as shown in execution_table row 6.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What index entries exist?

A Only dept_id=10 pointing to row 1

B dept_id=10 pointing to row 1 and dept_id=20 pointing to row 2

C dept_id=30 pointing to row 1 only

D No index entries yet

💡 Hint

Check the 'Index State' column in execution_table row 3

At which step does the index remove an entry due to a row deletion?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Look for 'Delete row' action in execution_table

If we insert a new row with dept_id=30 after step 5, what happens to the index entries?

A A new entry for dept_id=30 is added alongside existing one

B The index removes all entries for dept_id=30

C No change to index entries

D The index is rebuilt from scratch

💡 Hint

Refer to how index entries are updated on insert in execution_table rows 2 and 3

Concept Snapshot

Index maintenance in MySQL:
- Indexes are created with CREATE TABLE or ALTER TABLE
- Index entries update automatically on INSERT, UPDATE, DELETE
- Updates remove old index entries and add new ones
- Deletions remove index entries
- Queries use indexes to find data faster
- Indexes must always reflect current table data

Full Transcript

Index maintenance in MySQL means keeping the index data structures up to date as table data changes. When you create a table with an index, the index starts empty. When you insert rows, the index adds entries pointing to those rows. If you update a row's indexed column, the index removes the old entry and adds a new one with the updated value. When you delete a row, the index removes its entries. This maintenance ensures queries using the index run faster by quickly locating rows. The execution table shows each step: creating the table, inserting rows, updating a row, deleting a row, and querying using the index. The variable tracker shows how index entries and table rows change after each step. Key moments clarify why index entries change on updates and deletions. The quiz tests understanding of index state at different steps and effects of data changes on the index.