Creating indexes in MySQL - Try It Yourself

Creating Indexes in MySQL

📖 Scenario: You are managing a small online bookstore database. The books table stores information about each book, including its id, title, and author. To help the database find books faster when searching by title, you want to create an index on the title column.

🎯 Goal: Build a MySQL script that creates a books table and then adds an index on the title column to speed up searches.

📋 What You'll Learn

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

Add an index named idx_title on the title column.

Use standard MySQL syntax for table creation and index creation.

💡 Why This Matters

🌍 Real World

Indexes help databases find data faster, which is important for websites and apps that need quick responses.

💼 Career

Database administrators and backend developers often create and manage indexes to improve application performance.

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 a varchar(100), and author as a varchar(100).

MySQL

-- Create the books table with id, title, and author columns
-- Your code here

Need a hint?

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

2

Prepare to add an index

Add a comment line that says -- Prepare to add index on title to your script after the table creation statement.

MySQL

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

Need a hint?

Just add a comment line exactly as shown.

3

Create an index on the title column

Write a MySQL statement to create an index named idx_title on the title column of the books table.

MySQL

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

Need a hint?

Use CREATE INDEX idx_title ON books(title); to add the index.

4

Complete the script with a comment

Add a comment line at the end of the script that says -- Index created successfully.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100)
);
-- Prepare to add index on title
CREATE INDEX idx_title ON books(title);
-- Index created successfully
-- Your code here

Need a hint?

Just add the comment line exactly as shown.