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) |