mysql

Khóa học miễn phí MySQL – Show Indexes nhận dự án làm có lương

Posted on by user

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

mysql_statements_reference.htm

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user