MySQL - Drop Index

Table of content

The DROP statement in MySQL database is used to remove or delete an existing database object such as a table, index, view, or procedure. Whenever we use DROP statement with any of the database objects, like indexes, it will remove them permanently along with their associated data.

Therefore, we can drop any index from a database table using two different SQL DROP queries.

It is important to understand that dropping an index can have a significant impact on the performance of your database queries. Therefore, only try to remove an index if you are sure that it is no longer required.

The MySQL DROP INDEX Statement

The DROP INDEX statement in MySQL is used to delete an index from a table.

Syntax

Following is the syntax to drop an index using DROP INDEX statement −

DROP INDEX index_name ON table_name;

Example

In this example, we first create a new table CUSTOMERS and adding an index to one of its columns (AGE) 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(AGE)
);

Now, create another index on CUSTOMERS table. We are using CREATE INDEX statement here −

CREATE INDEX NAME_INDEX ON CUSTOMERS (Name);

DROP INDEX Query −

Then, use the following query to drop the index created above.

DROP INDEX NAME_INDEX ON CUSTOMERS;

Verification

To verify if the index has been dropped, display the table definition using DESC query below −

DESC CUSTOMERS;

As we can see in the following table, the index on NAME column is dropped.

Field Type Null Key Default Extra
ID int NO PRI NULL
NAME varchar(20) NO NULL
AGE int NO MUL NULL
ADDRESS char(25) YES NULL
SALARY decimal(18, 2) YES NULL

The MySQL ALTER... DROP Statement

The ALTER DROP statement can also be used to drop an index in a MySQL table. This is just an alternative to the DROP INDEX statement, so it only works with the index that exists on a table.

Syntax

Following is the syntax of the DROP INDEX IF EXISTS in SQL −

ALTER TABLE table_name DROP INDEX index_name;

Example

Let us see another example to drop the index from the CUSTOMERS table using the ALTER... DROP command as shown below −

ALTER TABLE CUSTOMERS DROP INDEX AGE;

Verification

To verify if the index on AGE column has been dropped, display the table definition using DESC query below −

DESC CUSTOMERS;

As we can see in the following table, the index on NAME column is dropped.

Field Type Null Key Default Extra
ID int NO PRI NULL
NAME varchar(20) NO NULL
AGE int NO NULL
ADDRESS char(25) YES NULL
SALARY decimal(18, 2) YES NULL

Dropping PRIMARY KEY or UNIQUE Constraint

The DROP INDEX statement in MySQL does not usually drop indexes like PRIMARY KEY or UNIQUE constraints. To drop indexes associated with these constraints, we need to use the ALTER TABLE DROP command.

Syntax

Following is the syntax −

ALTER TABLE table_name DROP constraint_name;

Example

In this example, we are using the following query to drop the PRIMARY KEY constraint present on the ID column of CUSTOMERS table −

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Verification

To verify whether the primary key constraint is dropped from the table, describe the 'temp' table using DESC command as follows −

DESC CUSTOMERS;

The PRIMARY KEY constraint is finally dropped! Look at the table below −

Field Type Null Key Default Extra
ID int NO NULL
NAME varchar(20) NO NULL
AGE int NO NULL
ADDRESS char(25) YES NULL
SALARY decimal(18, 2) YES NULL

Dropping an Index Using a Client Program

We have seen how to drop an index from a MySQL database using SQL queries. In addition to it, we can also use other client programs to perform the drop index operation in the MySQL database.

Syntax

Following are the syntaxes to drop an index from a MySQL database using various programming languages −

PHP NodeJS Java Python

The MySQL PHP connector mysqli provides a function named query() to execute the DROP INDEX query in the MySQL database.

$sql = "DROP INDEX index_name ON tbl_name";
$mysqli->query($sql);

The MySQL NodeJS connector mysql2 provides a function named query() to execute the DROP INDEX query in the MySQL database.

sql= "DROP INDEX index_name ON tbl_name";
con.query(sql);

We can use the JDBC type 4 driver to communicate to MySQL using Java. It provides a function named executeUpdate() to execute the DROP INDEX query in the MySQL database.

String sql = "DROP INDEX index_name ON table_name";
statement.executeQuery(sql);

The MySQL Connector/Python provides a function named execute() to execute the DROP INDEX query in the MySQL database.

drop_index_query = "DROP INDEX index_name ON tbl_name"
cursorObj.execute(drop_index_query);

Example

Following are the implementations of this operation in various programming languages −

PHP NodeJS Java Python 
$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.
');

// CREATE INDEX
$sql = "DROP INDEX tid ON tutorials_table";
if ($mysqli->query($sql)) {
    printf("Index droped successfully!.
");
}
if ($mysqli->errno) {
    printf("Index could not be droped!.
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Index droped successfully!.

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(255), age INT, Location VARCHAR(255));"
  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);
    console.log("--------------------------");
  });

  //Dropping Indexes
  sql = "DROP INDEX sample_index ON temp;"
  con.query(sql);

  sql = "DROP INDEX composite_index ON temp;"
  con.query(sql);

  //Displaying Indexes after deleting
  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.Statement;

public class DropIndex {
   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...!");

         //Drop index;
         String sql = "DROP INDEX tid ON tutorials_tbl";
         statement.executeUpdate(sql);
         System.out.println("Index has been dropped Successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Index has been dropped Successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
drop_index_query = "DROP INDEX idx_submission_date ON tutorials_tbl"
cursorObj.execute(drop_index_query)
connection.commit()
print("Index dropped successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Index dropped successfully.
Advertisements