Dropping an Index of a MySQL InnoDB Table using Python

A MySQL InnoDB can be deleted through the drop index option of the alter table SQL statement. The Python example creates a redundant secondary index on a column and deletes it.

MySQL Updated 23 Jun 2024 2 min read

Overview

  • An index makes database queries faster by avoiding of scanning the table from the beginning till the data is found.
  • A database index which is no longer required can be deleted through the SQL statement DROP INDEX.
  • In case a duplicate index has been created on to a table using Add Index option of the Alter Table SQL statement, it can be deleted through the SQL statement DROP INDEX. The SQL statement Create Index requires a name to be provided for the index being created. Hence creating a duplicate index is not possible with the SQL statement CREATE INDEX.

Example

python
# Example Python program to delete a redundant index

# MySQL client import

            import pymysql

# Create a connection to the MySQL Server

            dbServerIP          = "127.0.0.1"   # MySQL DB server IP Address

            dbUsrName           = "root"        # MySQL User name

            dbUsrPwd            = "BanniInside1$"            # MySQL Password of the db user

            charSet             = "utf8mb4"     # Character set

            dbCursorType        = pymysql.cursors.DictCursor # Cursor type to be used

dbSvrCon   = pymysql.connect(host=dbServerIP, user=dbUsrName, password=dbUsrPwd,

                                         charset=charSet, cursorclass=dbCursorType)

try:

                # Cursor object

                dbCur        = dbSvrCon.cursor()

    # SQL Statement - create table

                createTableSQL = """create table workplace.Albums(Id INT NOT NULL AUTO_INCREMENT,

                                    Album VARCHAR(128),

                                    Artist VARCHAR(128),

                                    Released DATE,

                                    Genre  VARCHAR(128),

                                    Length TIME,

                                    Producer VARCHAR(128),

                                    PRIMARY KEY (Id)

                                    )"""

    # Execute the sql statement

                dbCur.execute(createTableSQL)

    # SQL Statement - create secondary index

                sqlCreateIndex = """ALTER TABLE workplace.Albums ADD index (Album)"""

    # Execute the sql - create secondary index

                dbCur.execute(sqlCreateIndex)

    # Redundant index is created

                dbCur.execute(sqlCreateIndex)

    # List indexes

                sqlShowIndexes = "show index from workplace.Albums";

                dbCur.execute(sqlShowIndexes)

                indexList = dbCur.fetchall()

    print(indexList)

    ########## Drop the redundant Index ##########

                # Redundant indexes is created with a suffix of _2, _3 and so on.

                sqlDropIndex = "ALTER TABLE workplace.Albums DROP index Album_2"

                dbCur.execute(sqlDropIndex)

    # Print the index list

                dbCur.execute(sqlShowIndexes)

                indexList = dbCur.fetchall()

                print(indexList)

except Exception as e:

                print("Exception occured:{}".format(e))

finally:

                dbSvrCon.close()

Output

Output
[{'Table': 'Albums', 'Non_unique': 0, 'Key_name': 'PRIMARY', 'Seq_in_index': 1, 'Column_name': 'Id', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': '', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': '', 'Visible': 'YES', 'Expression': None}, {'Table': 'Albums', 'Non_unique': 1, 'Key_name': 'Album', 'Seq_in_index': 1, 'Column_name': 'Album', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': 'YES', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': '', 'Visible': 'YES', 'Expression': None}, {'Table': 'Albums', 'Non_unique': 1, 'Key_name': 'Album_2', 'Seq_in_index': 1, 'Column_name': 'Album', 'Collation': 'A', 'Cardinality': 0, 'Sub_part': None, 'Packed': None, 'Null': 'YES', 'Index_type': 'BTREE', 'Comment': '', 'Index_comment': '', 'Visible': 'YES', 'Expression': None}]

...

...

...