Show indexes in MySQL table

Question

If I define a MySQL index over two fields, how do I find out, which two belong together (using MySQL commands).

Here is an example table:

mysql> DESCRIBE lansuite_wiki_versions;
+-----------+-----------------------+------+-----+-------------------+-----------------------------+
| Field     | Type                  | Null | Key | Default           | Extra                       |
+-----------+-----------------------+------+-----+-------------------+-----------------------------+
| versionid | int(11)               | NO   | PRI | 0                 |                             |
| postid    | int(11)               | NO   | PRI | 0                 |                             |
| date      | timestamp             | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| userid    | mediumint(8) unsigned | NO   | MUL | 0                 |                             |
| text      | text                  | NO   | MUL | NULL              |                             |
| test1     | int(11)               | NO   | MUL | NULL              |                             |
| test2     | int(11)               | NO   |     | NULL              |                             |
+-----------+-----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

This table has indexes defined over:

• versionid + postid
• userid
• test1 + test2
• text

I know this, because I have assigned them and see them in phpmyadmin. But I want to see it in my application as well. So I found this mySQL command:

mysql> SHOW INDEX FROM lansuite_wiki_versions;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| lansuite_wiki_versions |          0 | PRIMARY  |            1 | versionid   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| lansuite_wiki_versions |          0 | PRIMARY  |            2 | postid      | A         |         144 |     NULL | NULL   |      | BTREE      |         |
| lansuite_wiki_versions |          1 | userid   |            1 | userid      | A         |           4 |     NULL | NULL   |      | BTREE      |         |
| lansuite_wiki_versions |          1 | test     |            1 | test1       | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| lansuite_wiki_versions |          1 | test     |            2 | test2       | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| lansuite_wiki_versions |          1 | text     |            1 | text        | NULL      |           1 |     NULL | NULL   |      | FULLTEXT   |         |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

But how do I see versionid + postid is connected? I can see Seq_in_index counting up. So can I rely on that versionid and postid form a common index, just because they are standing in rows next to each other in this output and the Seq_in_index countin up? Or is there an other command, that shows me which indexes are defined?

Answer 1

The key name column shows which index is being described. The sequence in index shows which order the columns are in, in that index. So in your example, you have a PRIMARY KEY on versionid and postid, a non-unique key userid on userid, a non-unique key test on test1 and test2, etc.

Answer 2

The Key_name will be unique for each index; columns which are part of the same index will have the same name in this table.

seq_in_index gives you the sequence.

It may make more sense if you look at the INFORMATION_SCHEMA table containing indexes (look at documentation).

I assume you are writing a tool to programmatically inspect the database structure.

If you are a human and want to see the table structure, I recommend SHOW CREATE TABLE instead.