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 -pUse the
worlddatabase:mysql> USE world;- Create an Index Called `population` on the `city` Table Using the `Population` Column
Run the following command to create the
populationindex: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
primarylanguageindex: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
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
capitalindex:mysql> DROP INDEX capital ON country;
