Creating Indexes in MySQL

1 hour
  • 5 Learning Objectives

About this Hands-on Lab

The MySQL server allows us to create indexes, which improves the speed of operations within a database table. In this Lab, we will be creating new indexes on existing tables, creating a new table with an index, and deleting an existing index from a table. Completing this lab will demonstrate a working knowledge of indexes in MySQL.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log into the MySQL Server as root and Use the world Database

Log into the MySQL server, using Linux4me! as a root MySQL password:

# mysql -u root -p

Use the world database:

mysql> USE world;
Create an Index Called `population` on the `city` Table Using the `Population` Column

Run the following command to create the population index:

mysql> CREATE INDEX population ON city(Population);
Create an Index Called `primarylanguage` on the `countrylanguage` Table Using the `Language` and `IsOfficial` Columns

Use the following command to create the primarylanguage index:

mysql> CREATE INDEX primarylanguage ON countrylanguage(Language,IsOfficial);
Create a New Table Named `countryexports` in the `world` Database Using the Information Provided in the Instructions

Create the countryexports table using the following command:

mysql> CREATE TABLE countryexports (Code CHAR(3) PRIMARY KEY, Name CHAR(52), Export VARCHAR(120), Quantity INT, INDEX export (Export));
Delete the `capital` Index on the `country` Table

Run the following command to delete the capital index:

mysql> DROP INDEX capital ON country;

Additional Resources

Our team has been tasked with optimizing tables within the world database. In order to accomplish this we'll need to create an index called population on the city table, using the Population column. Then we need to create an index called primarylanguage on the countrylanguage table, using the Language and IsOfficial columns.

Next, we'll have to create a new table called countryexports, with the following characteristics:

  • The first column should be named Code using the CHAR(3) datatype and set as the primary key.
  • The second column should be named Name and use the CHAR(52) datatype
  • The third column should be named Export and use the VARCHAR(120) data type
  • The fourth column should be named Quantity and use the INT datatype
  • The table should have an index named export using the Export table.

After all this, we need to remove the capital index from the country table, since it is no longer needed.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!

Get Started
Who’s going to be learning?