MySQL Database Indexing | Database Management

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) mysql(("`MySQL`")) -.-> mysql/AdvancedQueryingandOptimizationGroup(["`Advanced Querying and Optimization`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"]) sql(("`SQL`")) -.-> sql/DatabaseManagementandOptimizationGroup(["`Database Management and Optimization`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/alter_table("`Table Modification`") mysql/AdvancedQueryingandOptimizationGroup -.-> mysql/index("`Index Management`") sql/BasicSQLCommandsGroup -.-> sql/alter_table("`ALTER TABLE statements`") sql/DataDefinitionandIntegrityGroup -.-> sql/constraints("`Constraints`") sql/DatabaseManagementandOptimizationGroup -.-> sql/creating_indexes("`Creating Indexes`") subgraph Lab Skills mysql/source -.-> lab-301274{{"`Managing Database Indexes in MySQL`"}} mysql/alter_table -.-> lab-301274{{"`Managing Database Indexes in MySQL`"}} mysql/index -.-> lab-301274{{"`Managing Database Indexes in MySQL`"}} sql/alter_table -.-> lab-301274{{"`Managing Database Indexes in MySQL`"}} sql/constraints -.-> lab-301274{{"`Managing Database Indexes in MySQL`"}} sql/creating_indexes -.-> lab-301274{{"`Managing Database Indexes in MySQL`"}} end

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.

Open a terminal and run the following command to start the MySQL service:
```
sudo /etc/init.d/mysql start
```
Log into the MySQL terminal:
```
mysql -uroot
```
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.

Create a new file named addIndex.sql in the ~/project directory.
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.

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.

Managing Database Indexes in MySQL