MySQL – Show Indexes
Table of content
”;
A MySQL Index is a type of special lookup table that is used to make data retrieval easier in a database. It points to the actual data in the database.
MySQL allows various types of indexes to be created on one or more columns in a table. They are:
-
Primary Key Index
-
Unique Index
-
Simple Index
-
Composite Index
-
Implicit Index
To check if any of these indexes are defined on a table or not, MySQL provides the SHOW INDEX statement.
The MySQL SHOW INDEX Statement
The SHOW INDEX Statement of MySQL is used to list out the information about table index.
The vertical-format output (specified by G) in MySQL often is used with this statement, to avoid a long line wraparound.
Syntax
Following is the basic syntax of the SHOW INDEX Statement −
SHOW INDEX FROM table_name;
Example
In this example, we are create a new table CUSTOMERS and adding a PRIMARY KEY index to one of its columns using the following CREATE TABLE query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY(ID), INDEX(NAME) );
Now, we can display the indexes present on the CUSTOMERS table using the following SHOW INDEX query −
SHOW INDEX FROM CUSTOMERSG
Output
The vertical-output will be displayed as −
*************************** 1. row ************************ Table: customers Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ID Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row ************************ Table: customers Non_unique: 1 Key_name: NAME Seq_in_index: 1 Column_name: NAME Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.01 sec)
With IN Clause
In this example, let us first create an index on the AGE column of CUSTOMERS table using the following CREATE INDEX query −
CREATE INDEX AGE_INDEX ON CUSTOMERS (AGE);
You can also retrieve the information by specifying the database name as −
SHOW INDEX IN CUSTOMERS FROM sampleG
Output
The output will be the same as above −
*************************** 1. row *************************** Table: customers Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ID Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: customers Non_unique: 1 Key_name: NAME Seq_in_index: 1 Column_name: NAME Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.01 sec)
With WHERE Clause
As the indexes are displayed in a table format, we can use a WHERE clause with SHOW INDEX statement to retrieve specified indexes matching a given condition.
SHOW INDEX IN CUSTOMERS WHERE Column_name = ''NAME''G
Output
The index created on NAME column is displayed −
*************************** 1. row ************************ Table: customers Non_unique: 1 Key_name: NAME Seq_in_index: 1 Column_name: NAME Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 1 row in set (0.00 sec)
Show Indexes Using Client Program
We can also display index information on a MySQL table using a client program.
Syntax
Following are the syntaxes to show indexes on a MySQL table using various programming languages −
To show an index from MySQL table through a PHP program, we need to execute the SHOW INDEX statement using the query() function provided by mysqli connector as follows −
$sql = "SHOW INDEX FROM tutorials_table"; $mysqli->query($sql);
To show an index from MySQL table through a JavaScript program, we need to execute the SHOW INDEX statement using the query() function of mysql2 library as follows −
sql = "SHOW INDEXES FROM temp"; con.query(sql);
To show an index from MySQL table through a Java program, we need to execute the SHOW INDEX statement using the executeQuery() function of JDBC as follows −
String sql = "SHOW INDEXES FROM tutorials_tbl"; st.executeQuery(sql);
To show an index from MySQL table through a Python program, we need to execute the SHOW INDEX statement using the execute() function of the MySQL Connector/Python as follows −
rename_view_query = "SHOW INDEXES FROM tutorials_tbl" cursorObj.execute(rename_view_query)
Example
Following are the programs −
$dbhost = ''localhost''; $dbuser = ''root''; $dbpass = ''password''; $dbname = ''TUTORIALS''; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf(''Connected successfully.
''); // SHOW INDEX $sql = "SHOW INDEX FROM tutorials_table"; if ($index = $mysqli->query($sql)) { printf("Index shown successfully!.
"); while ($indx = mysqli_fetch_row($index)) { print_r($indx); } } if ($mysqli->errno) { printf("Index could not be shown!.
", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
Index shown successfully!. Array ( [0] => tutorials_tbl [1] => 0 [2] => PRIMARY [3] => 1 [4] => tutorial_id [5] => A [6] => 3 [7] => [8] => [9] => [10] => BTREE [11] => [12] => [13] => YES [14] => ) Array ( [0] => tutorials_tbl [1] => 0 [2] => UIID [3] => 1 [4] => tutorial_id [5] => A [6] => 3 [7] => [8] => [9] => [10] => BTREE [11] => [12] => [13] => YES [14] => )
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "create database TUTORIALS" con.query(sql); sql = "USE TUTORIALS" con.query(sql); sql = "CREATE TABLE temp (ID INT, Name VARCHAR(100), Age INT, City VARCHAR(100));" con.query(sql); sql = "INSERT INTO temp values(1, ''Radha'', 29, ''Vishakhapatnam''), (2, ''Dev'', 30, ''Hyderabad'');" con.query(sql); //Creating Indexes sql = "CREATE INDEX sample_index ON temp (name) USING BTREE;" con.query(sql); sql = "CREATE INDEX composite_index on temp (ID, Name);" con.query(sql); //Displaying Indexes sql = "SHOW INDEXES FROM temp;" con.query(sql, function(err, result){ if (err) throw err console.log(result); }); });
Output
The output produced is as follows −
Connected! -------------------------- [ { Table: ''temp'', Non_unique: 1, Key_name: ''sample_index'', Seq_in_index: 1, Column_name: ''Name'', Collation: ''A'', Cardinality: 2, Sub_part: null, Packed: null, Null: ''YES'', Index_type: ''BTREE'', Comment: '''', Index_comment: '''', Visible: ''YES'', Expression: null }, { Table: ''temp'', Non_unique: 1, Key_name: ''composite_index'', Seq_in_index: 1, Column_name: ''ID'', Collation: ''A'', Cardinality: 2, Sub_part: null, Packed: null, Null: ''YES'', Index_type: ''BTREE'', Comment: '''', Index_comment: '''', Visible: ''YES'', Expression: null }, { Table: ''temp'', Non_unique: 1, Key_name: ''composite_index'', Seq_in_index: 2, Column_name: ''Name'', Collation: ''A'', Cardinality: 2, Sub_part: null, Packed: null, Null: ''YES'', Index_type: ''BTREE'', Comment: '''', Index_comment: '''', Visible: ''YES'', Expression: null } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class ShowIndex { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Show index...!; String sql = "SHOW INDEXES FROM tutorials_tbl"; ResultSet resultSet = statement.executeQuery(sql); System.out.println("Following are the indexes in tutorials_tbl"); while (resultSet.next()){ System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2) +" "+resultSet.getString(3)+" " + resultSet.getString(4)); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Following are the indexes in tutorials_tbl tutorials_tbl 0 PRIMARY 1 tutorials_tbl 1 tid 1
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) table_name = ''tutorials_tbl'' cursorObj = connection.cursor() show_indexes_query = f"SHOW INDEXES FROM {table_name}" cursorObj.execute(show_indexes_query) indexes = cursorObj.fetchall() for index in indexes: print(f"Table: {index[2]}, Index Name: {index[3]}, Column Name: {index[4]}, Non-unique: {index[1]}") cursorObj.close() connection.close()
Output
Following is the output of the above code −
Table: PRIMARY, Index Name: 1, Column Name: tutorial_id, Non-unique: 0 Table: idx_submission_date, Index Name: 1, Column Name: submission_date, Non-unique: 1
”;