Page 116 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

EXERCISE 30 : Indexing and Optimizing query

Objectives

At the end of this exercise you shall be able to
• create and use the index
• identify slow queries
• optimize query executive
Requirements

Tools/Materials
• Desktop/Laptop with latest configuration
• Operating system: window 10:11
• XAMPP server r3.3.0

Procedure

TASK 1 : INDEXING:
1 Open MySQL Command Line-
Open the MySQL Command Line or any MySQL client that allows you to execute SQL commands.
2 Connect to your database-
mysql -u your username p

Enter your password when prompted
3 Select Your Database-
USE your database name;
Identify the column to be indexed

Let’s assume you want to create an index on a column named example column in a table named your table.
4 Create the Index-
CREATE INDEX idx example column ON your table(example column);
5 Verify the Index-
SHOW INDEX FROM your table;
This command displays information about the indexes on the specified table. You should see the newly created
index in the output
Example Output –
+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
-+---------+---------------+
| Table | Non unique | Key name | Seq in index | Column name | Collation | Cardinality | Sub part | Packed
| Null | Index type | Comment | Index comment |
+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
-+---------+---------------+

| your table | 1 | idx example column | 1 | example column | A | 0 | NULL | NULL |
YES | BTREE | | |
+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
-+---------+---------------+

101
CITS : IT & ITES - Computer Software Application - Exercise 29