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.

Practice

1. What is the main purpose of maintaining indexes in a MySQL database?

easy

A. To speed up data searches and queries

B. To increase the size of the database

C. To delete all data from tables

D. To backup the database automatically

Solution

1. Step 1: Understand what indexes do

   Indexes help the database find data faster, like an index in a book.

2. Step 2: Identify the main benefit of index maintenance

   Maintaining indexes keeps searches quick and efficient.

3. Final Answer:

   To speed up data searches and queries -> Option A

4. Quick Check:

   Indexes improve search speed = D [OK]

Hint: Indexes speed up queries by organizing data [OK]

Common Mistakes:

• Thinking indexes increase database size only
• Confusing index maintenance with data backup
• Believing indexes delete data

2. Which of the following is the correct syntax to drop an index named idx_name from a table users in MySQL?

easy

A. DELETE INDEX idx_name FROM users;

B. DROP INDEX idx_name ON users;

C. REMOVE INDEX idx_name IN users;

D. DROP idx_name FROM users;

Solution

1. Step 1: Recall MySQL syntax for dropping an index

   The correct command is DROP INDEX index_name ON table_name;.

2. Step 2: Match the syntax with options

   DROP INDEX idx_name ON users; matches the correct syntax exactly.

3. Final Answer:

   DROP INDEX idx_name ON users; -> Option B

4. Quick Check:

   DROP INDEX ... ON ... is correct syntax = B [OK]

Hint: Use DROP INDEX index_name ON table_name; to remove indexes [OK]

Common Mistakes:

• Using DELETE or REMOVE instead of DROP
• Omitting ON keyword
• Wrong keyword order

3. Given the table products with an index on category_id, what will be the effect of running OPTIMIZE TABLE products;?

medium

A. It renames the products table

B. It deletes all rows from the products table

C. It adds a new index on category_id automatically

D. It rebuilds the table and its indexes to improve performance

Solution

1. Step 1: Understand what OPTIMIZE TABLE does

   OPTIMIZE TABLE rebuilds the table and its indexes to reduce fragmentation and improve speed.

2. Step 2: Analyze the options

   Only It rebuilds the table and its indexes to improve performance correctly describes this effect.

3. Final Answer:

   It rebuilds the table and its indexes to improve performance -> Option D

4. Quick Check:

   OPTIMIZE TABLE rebuilds table and indexes = A [OK]

Hint: OPTIMIZE TABLE rebuilds indexes and table for better speed [OK]

Common Mistakes:

• Thinking OPTIMIZE deletes data
• Assuming it adds indexes automatically
• Confusing it with renaming tables

4. You tried to drop an index using DROP INDEX idx_user ON users; but got an error. What is a likely cause?

medium

A. The table users does not exist

B. You must use DELETE INDEX instead of DROP INDEX

C. The index name idx_user does not exist on the table

D. You need to restart the MySQL server before dropping indexes

Solution

1. Step 1: Check common reasons for DROP INDEX errors

   An error usually occurs if the index name is wrong or missing.

2. Step 2: Evaluate the options

   The index name idx_user does not exist on the table is the most common cause; other options are incorrect commands or unnecessary actions.

3. Final Answer:

   The index name idx_user does not exist on the table -> Option C

4. Quick Check:

   Wrong index name causes DROP INDEX error = C [OK]

Hint: Verify index name exists before dropping it [OK]

Common Mistakes:

• Using DELETE instead of DROP
• Assuming server restart is needed
• Not checking table existence first

5. You have a large table orders with a composite index on (customer_id, order_date). After frequent inserts and deletes, queries on customer_id are slow. What is the best index maintenance step to improve performance?

hard

A. Run OPTIMIZE TABLE orders; to rebuild the table and indexes

B. Drop the composite index and create separate indexes on each column

C. Delete all rows and reload the table

D. Rename the table to refresh indexes

Solution

1. Step 1: Understand index fragmentation impact

   Frequent inserts and deletes cause index fragmentation, slowing queries.

2. Step 2: Choose the best maintenance action

   OPTIMIZE TABLE rebuilds indexes and reduces fragmentation, improving query speed.

3. Step 3: Evaluate other options

   Dropping and recreating indexes separately may not help and can be costly; deleting data or renaming table is unrelated.

4. Final Answer:

   Run OPTIMIZE TABLE orders; to rebuild the table and indexes -> Option A

5. Quick Check:

   OPTIMIZE TABLE fixes fragmentation = A [OK]

Hint: Use OPTIMIZE TABLE to fix fragmented indexes after many changes [OK]

Common Mistakes:

• Dropping composite index without reason
• Thinking renaming table refreshes indexes
• Deleting data instead of maintaining indexes