Whats query can be used to get the details of Indexes of any table?

Question

Whats query can be used to get the details of Indexes of any table? I need this to find out primarykey/autoincremented value of any table.. Please help/Guide me...

Answer 1

You can use

show indexes from your_table;

For more informations : 12.4.5.23. SHOW INDEX Syntax

As a quick demo (on a not-quite-optimized table) :

mysql> show indexes from post;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| post  |          0 | PRIMARY         |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         | 
| post  |          1 | id_blog_idx     |            1 | id_blog     | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
| post  |          1 | id_user_idx     |            1 | id_user     | A         |           7 |     NULL | NULL   |      | BTREE      |         | 
| post  |          1 | code_syntax_idx |            1 | code_syntax | A         |           7 |     NULL | NULL   |      | BTREE      |         | 
| post  |          1 | code_status_idx |            1 | code_status | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
| post  |          1 | id_category_idx |            1 | id_category | A         |           7 |     NULL | NULL   |      | BTREE      |         | 
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0,00 sec)

Note, though, that this will display indexes -- and auto_increment doesn't have much to do with indexes.

If you want to see the auto_increment of your table, you can use desc :

desc your_table;

For more informations : 12.8.1. DESCRIBE Syntax

And, for example, with the same table :

mysql> desc post;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| id_blog            | int(10) unsigned | NO   | MUL | NULL    |                | 
| id_user            | int(10) unsigned | NO   | MUL | NULL    |                | 
...
...
| nb_comments        | smallint(6)      | NO   |     | 0       |                | 
+--------------------+------------------+------+-----+---------+----------------+
17 rows in set (0,05 sec)

Answer 2

Also, an alternate way (which also shows you the current value of the AUTO_INCREMENT counter) is:

=> SHOW CREATE TABLE activations;

Yielding

CREATE TABLE `activations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=104974 DEFAULT CHARSET=utf8