CODEWITHSUNDEEP
phpmysqlindexing
JochenJung
JochenJung

Reputation: 7213

Show indexes in MySQL table

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:

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?

Upvotes: 2

Views: 7900

Answers (3)

Daniele
Daniele

Reputation: 89

You should not trust in the query SHOW INDEX FROM table , sometimes it miss one or more indexes. I prefer:

SELECT DISTINCT
INDEX_NAME  
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'DBNAME' and TABLE_NAME='TABLE';

Upvotes: 1

MarkR
MarkR

Reputation: 63616

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.

Upvotes: 2

Brian Hooper
Brian Hooper

Reputation: 22084

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.

Upvotes: 1

Related Questions