- A Cloud Guru
Creating Indexes in MySQL
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.
Table of Contents
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
mysql> USE world;
Create an Index Called `population` on the `city` Table Using the `Population` Column
Run the following command to create the
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
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
countryexportstable 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
mysql> DROP INDEX capital ON country;
What's a lab?
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.