my_indexr: Script to drop and recreate MySQL indexes
Sunday, January 12th, 2014
As can be read in this article, I was in need of a method to quickly drop all indexes (except primary keys) from a MySQL database. After googling around a bit and being astonished that apparently no-one had written such a thing yet, I wrote the script that can be seen in that article.
Unfortunately, that script wasn’t very good, so I decided to do a cleaner better implementation of it. The result is my_indexr, which spits out SQL commands to drop and recreate indexes on a database. Other features include:
- Process only certain tables
- Process non-primary or both normal and primary indexes
- Correctly handles:
- Primary key indexes
- Compound key / multi-column indexes
- Index types (BTREE, etc)
- Prefix lengths
- Auto_increment columns, which MUST be a key (my_indexr skips indexes with these columns in them)
There may still be some edge-cases that are not properly handled by my_indexr. If you encouter one, please let me know.
You can download my_indexr from its Bitbucket page.
This is what its output looks like:
$ ./indexr.py -u root -p mydb DROP INDEX `location` ON `idx_tst_innodb_basic`; DROP INDEX `name_age` ON `idx_tst_innodb_basic`; DROP INDEX `email` ON `idx_tst_innodb_basic`; DROP INDEX `PRIMARY` ON `idx_tst_innodb_compkey`; CREATE INDEX `location` USING BTREE ON `idx_tst_innodb_basic` (`location_id`); CREATE INDEX `name_age` USING BTREE ON `idx_tst_innodb_basic` (`name`(40),`age`); CREATE UNIQUE INDEX `email` USING BTREE ON `idx_tst_innodb_basic` (`email`); ALTER TABLE `idx_tst_innodb_compkey` ADD PRIMARY KEY (`last_name`,`first_name`);