Creating and dropping indexes in MySQL table

Advertisements

Indexes increases the speed of select queries. At the same time indexes decreases the insert query performance if there are lots of indexes in a table. How to add an index to MySQL table  and how to drop an index from a MySQL table? We will discuss both in this article.


For showing indexes in a table we can use show indexes from command. In this we can see that there is only one index that is primary index in the table based on the column employee_id



Show index :
mysql> show index from table_name;
+--------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table_name |          0 | PRIMARY  |            1 | employee_id | A         |         231 |     NULL | NULL   |      | BTREE      |         |
+--------------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

Now when we search with a parent_id it will scan all the 231 rows because there is no index associated with parent_id column.
mysql> explain SELECT*  FROM table_name  WHERE parent_id = '23';
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | widget_stats | ALL  | NULL          | NULL | NULL    | NULL |  231 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Creating the index :
Now we will add an index parent_id_index on the tables to the column prent_id
mysql> create index parent_id_index on table_name (parent_id);
Query OK, 231 rows affected (0.03 sec)
Records: 231  Duplicates: 0  Warnings: 0

Now we can see that there are two indexes available in the table.
mysql> show indexes from table_name;
+--------------+------------+---------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name      | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+---------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table_name |          0 | PRIMARY       |            1 | employee_id | A         |         231 |     NULL | NULL   |      | BTREE      |         |
| Table_name  |          1 | parent_id_index |            1 | parent_id    | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |
+--------------+------------+---------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Now repeating the select with explain. We can see that it took only 5 comparisons instead of 231( when there was no index associated with parent_id)
mysql> explain SELECT *  FROM  Table_name  WHERE parent_id = '23';
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      |  Table_name  | ref  | parent_id_index | parent_id_index | 5       | const |    2 | Using where |
+----+-------------+--------------+------+---------------+---------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Dropping the index :
You can drop an index by using the following command. 
Syntax:
DROP INDEX index_name ON table_name
Eg:
mysql> DROP INDEX parent_id_index ON   Table_name;

Query OK, 231 rows affected (0.03 sec)
Records: 231  Duplicates: 0  Warnings: 0

---

Recommended Reading

1. High Performance MySQL: Optimization, Backups, and Replication
2. MySQL (4th Edition)

---