Advertisements
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 Replication2. MySQL (4th Edition)
No comments:
Post a Comment
Be nice. That's all.