MySQL - Show Indexes

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)

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