Index maintenance in MySQL - Mini Project: Build & Apply

Index Maintenance in MySQL

📖 Scenario: You are managing a small online bookstore database. To keep the database fast and efficient, you need to create and maintain indexes on important columns.

🎯 Goal: Build a MySQL script that creates a table, adds an index, and then maintains that index by dropping and recreating it.

📋 What You'll Learn

Create a table named books with columns id (integer primary key), title (varchar 100), and author (varchar 100).

Add an index named idx_author on the author column.

Drop the index idx_author.

Recreate the index idx_author on the author column.

💡 Why This Matters

🌍 Real World

Indexes help databases find data faster, just like an index in a book helps you find pages quickly.

💼 Career

Database administrators and developers use index maintenance to keep applications running smoothly and efficiently.

1

Create the books table

Write a MySQL statement to create a table called books with three columns: id as an integer primary key, title as varchar(100), and author as varchar(100).

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with column definitions and PRIMARY KEY for id.

2

Add an index on author

Write a MySQL statement to add an index named idx_author on the author column of the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);
-- Your code here to add index idx_author on author

Need a hint?

Use CREATE INDEX idx_author ON books(author); to add the index.

3

Drop the index idx_author

Write a MySQL statement to drop the index named idx_author from the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

CREATE INDEX idx_author ON books(author);
-- Your code here to drop index idx_author

Need a hint?

Use DROP INDEX idx_author ON books; to remove the index.

4

Recreate the index idx_author

Write a MySQL statement to recreate the index named idx_author on the author column of the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);

CREATE INDEX idx_author ON books(author);

DROP INDEX idx_author ON books;
-- Your code here to recreate index idx_author on author

Need a hint?

Use the same CREATE INDEX statement as before to recreate the index.