Script to Drop All Index Keys of a MySQL Database Except Primary Key

This script will produce the scripts to drop all index keys of a database:

#!/bin/sh

db=$3
user=$1
pswd=$2

mysql -u$user -p$pswd -e "show tables from $db"| awk '{if(NR>1) print "show index from "$1";"}'| mysql -u$user -p$pswd $db| awk '{if($3 !~ /Key_name/ && $3 !~ /PRIMARY/) print $1" "$3}'| awk '{print "alter table "$1" drop index "$2";"}'

Save the script as a shell script file and then execute by submitting 3 parameters:
1. Username
2. Password
3. Database name

Later, copy and paste the alter scripts via the MySQL client.

1 comments: (+add yours?)

Jude Allred said...

Tested.. works. Thanks, that was helpful.

Some of the drop index statements are duplicated in the output, but that effect is benign since MySql throws an error when attempting to delete a nonexistant index.