Skip to main content

Creating Indexes in MySQL

Hands-On Lab

 

Photo of Matthew Pearson

Matthew Pearson

Linux Training Architect II in Content

Length

01:00:00

Difficulty

Intermediate

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.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Creating Indexes in MySQL

Introduction

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.

The Scenario

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.

Getting Logged In

Use the credentials and server IP in the hands-on lab overview page to log into our lab server.

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);

Verify we got it done with:

mysql> SHOW INDEX FROM city;

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);

We can verify this is working with:

mysql> SHOW INDEX FROM countrylanguage;

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));

Check to see if the table is built correctly with:

mysql> DESCRIBE countryexports

We can check our indexes with this:

mysql> SHOW INDEX FROM countryexports;

Delete the capital Index on the country Table

Run the following command to delete the capital index:

mysql> DROP INDEX capital ON country;

We can check with this:

mysql> SHOW INDEX FROM country;

The index with a key named capital is gone.

Conclusion

Indexes will speed up our querying, and now we know how to set them up. Congratulations!