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.
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()
We'd like to use Google Analytics cookies to count visits and see which tutorials are useful.
They are only set if you accept. We don't use advertising cookies and we don't sell your data.
You can change your choice any time from “Your privacy choices” at the bottom of every page.
Privacy Policy
Your privacy choices
Your browser sent a Global Privacy Control signal, so analytics is off unless you turn it on here.
Strictly necessary
Remembers this choice in your browser. No cookies, nothing sent to anyone.Always on