#Determine whether a table is indexed
show index from [table name]
#Display a table’s index
mysql> show index from mysql.user;
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| user | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | |
| user | 0 | PRIMARY | 2 | User | A | 6 | NULL | NULL | | BTREE | | |
+——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
2 rows in set (0.01 sec)
#Add an index to a table
Turn on the slow log, and look for a slow query to index. Use the Describe command.
DESCRIBE
* It will tell you how many rows it had to examine and what method it used for matching.
* Using the where method, mysql will scan the entire table to find a match, pulling up each row and
* checking to see if the columns match its criteria.
* This is SLOW
To actually create the index
* Use CREATE INDEX to create an index on the table as follows:
CREATE INDEX <index_name> on <tbl_name> (<column_name>)
