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.
WordPress uses cookies, or tiny pieces of information stored on your computer, to verify who you are. There are cookies for logged in users and for commenters. These cookies expire two weeks after they are set.

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