How to list the indexes in a MySQL database table.

Posted: September 1, 2012. At: 5:12 PM. This was 5 years ago. Post ID: 4526
Page permalink: http://securitronlinux.com/bejiitaswrath/how-to-list-the-indexes-in-a-mysql-database-table/

How to list the indexes in a MySQL database table. Easily done using the show command.

mysql> show index from customer;
+----------+------------+-------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customer |          0 | PRIMARY           |            1 | cust_id_n          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | idx_cust_payterms |            1 | cust_paytermsid_fn | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| customer |          1 | idx_cust_salesman |            1 | cust_salesmanid_fn | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+-------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

Another example using another table in my database.

mysql> show index from address;
+---------+------------+------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| address |          0 | PRIMARY          |            1 | addr_id_in         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| address |          1 | idx_addr_cust    |            1 | addr_custid_fn     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| address |          1 | fk_addr_salesman |            1 | addr_salesmanid_fn | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

And this query will show all of the indexes in an entire database; this is very useful for visualizing what you have done with your database and keeping track of the indexes and primary keys.

mysql> SELECT table_name AS `Table`, index_name AS `Index`, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns` FROM information_schema.statistics WHERE table_schema = 'acme' GROUP BY 1,2;
+----------------+-----------------------+---------------------------------------------+
| Table          | Index                 | Columns                                     |
+----------------+-----------------------+---------------------------------------------+
| address        | fk_addr_salesman      | addr_salesmanid_fn                          |
| address        | idx_addr_cust         | addr_custid_fn                              |
| address        | PRIMARY               | addr_id_in                                  |
| customer       | idx_cust_payterms     | cust_paytermsid_fn                          |
| customer       | idx_cust_salesman     | cust_salesmanid_fn                          |
| customer       | PRIMARY               | cust_id_n                                   |
| discount       | PRIMARY               | disc_minamount_n,disc_maxamount_n           |
| order_header   | idx_ordhdr_cust       | ordhdr_custid_fn                            |
| order_header   | idx_ordhdr_ordnbr     | ordhdr_nbr_s                                |
| order_header   | idx_ordhdr_payterm    | ordhdr_payterms_fn                          |
| order_header   | idx_ordhdr_sales      | ordhdr_salesmanid_fn                        |
| order_header   | idx_ordhdr_status     | ordhdr_statusid_fn                          |
| order_line     | idx_ordline_ordhdr    | ordline_ordhdrid_fn                         |
| order_line     | idx_ordline_prod      | ordline_prodid_fn                           |
| order_line     | PRIMARY               | ordline_id_n                                |
| order_shipment | idx_ordship_ord       | ordship_ordhdr_id_fn                        |
| order_shipment | idx_ordship_ship      | ordship_shipment_id_fn                      |
| order_shipment | PRIMARY               | ordship_ordhdr_id_fn,ordship_shipment_id_fn |
| payment_terms  | PRIMARY               | payterms_id_n                               |
| phone          | idx_phone_cust        | phone_custid_fn                             |
| phone          | PRIMARY               | phone_id_n                                  |
| product        | PRIMARY               | prod_id_n                                   |
| reseller       | idx_reseller_ressupid | reseller_supplier_id                        |
| reseller       | PRIMARY               | reseller_id_n                               |
| salesman       | PRIMARY               | salesman_id_n                               |
| shipment       | PRIMARY               | shipment_id_n                               |
| status         | PRIMARY               | status_id_n                                 |
+----------------+-----------------------+---------------------------------------------+
27 rows in set (0.00 sec)

No comments have been made. Use this form to start the conversation :)

Leave a Reply