• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Show indexes for a table with MySQL

Show indexes for a table with MySQL

MySQL has a SQL query "SHOW INDEX FROM" which returns the indexes from a table. This post looks at some example usage of this query to get a list of indexes and a list of primary keys for a table with MySQL.

Example tables

The examples in this post have two tables which are structued as follows:

CREATE TABLE `products` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(100) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `visible` tinyint(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`product_id`),
  UNIQUE KEY `url` (`url`),
  KEY `visible` (`visible`)
)
CREATE TABLE `products_to_categories` (
  `product_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`product_id`,`category_id`)
)

Show the indexes

Running the following query will show all the indexes for the products table:

SHOW INDEXES FROM products;

This will return the following:

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products |          0 | PRIMARY  |            1 | product_id  | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| products |          0 | url      |            1 | url         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| products |          1 | visible  |            1 | visible     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Show the primary keys

To just show the primary key(s) for the products table run this query:

SHOW INDEXES FROM products WHERE Key_name = "PRIMARY";

This will return the following:

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products |          0 | PRIMARY  |            1 | product_id  | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Show the primary keys – multiple primary keys

The final example uses the products_to_categories table which has a multi-part primary key, so two rows are returned. The SQL:

SHOW INDEXES FROM products_to_categories WHERE Key_name = "PRIMARY";

And the result:

+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products_to_categories |          0 | PRIMARY  |            1 | product_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| products_to_categories |          0 | PRIMARY  |            2 | category_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Summary

It’s easy to show the indexes and primary keys for a table with MySQL. You can read more about the columns returned on the SHOW INDEX Syntax manual page in the MySQL manual.

Check Out These Related posts:

  1. RFC 1321 – MD5 Message-Digest Algorithm
  2. Get a MySQL table structure from the INFORMATION_SCHEMA
  3. Get a MySQL table structure with DESCRIBE
  4. Show indexes for a table with the MySQL INFORMATION_SCHEMA

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap