Managing Database Indexes in MySQL

Introduction

In this project, you will learn how to manage indexes in a MySQL database. Specifically, you will learn how to add an index to the title field of the course table in the edusys database.

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and log into the MySQL terminal
• How to import a SQL script into the MySQL database
• How to add an index to a table column
• How to save and execute a SQL script to create the index

🏆 Achievements

After completing this project, you will be able to:

• Understand the importance of indexing in a database for improving query performance
• Confidently add indexes to table columns in a MySQL database
• Apply your knowledge of SQL commands to manage the database structure

Start MySQL and Import the Database

In this step, you will learn how to start the MySQL server and import the edusys.sql database script into MySQL.

1. Open a terminal and run the following command to start the MySQL service:

   sudo /etc/init.d/mysql start
   

2. Log into the MySQL terminal:

   mysql -uroot
   

3. Import the edusys.sql script into the MySQL database:

   SOURCE ~/project/edusys.sql
   

Add an Index to the Course Table

In this step, you will learn how to add an index to the title field of the course table.

1. Create a new file named addIndex.sql in the ~/project directory.
2. In the addIndex.sql file, add an index to the title field of the course table with the index name ix_title:

ALTER TABLE edusys.course ADD INDEX ix_title (title);

Run the Index Addition Script

In this step, you will learn how to run the addIndex.sql script to create the index on the course table.

1. In the MySQL terminal, run the following command to execute the addIndex.sql script:

   SOURCE ~/project/addIndex.sql
   

   This will create the ix_title index on the title field of the course table.

Congratulations! You have successfully added an index to the course table in the edusys database.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.