Thanks for the feedback. What I am trying to do is two things: 1. Remove all indexes and make the database smaller to copy and move to another prod box. Currently my indexes are in the double digit GB! Yikes ;-)
2. Remove all indexes so I can find out which ones are needed then tell mysql to recreate them and apparently it lessen data fragmentation if it starts from scratch vs. turning on and off. I was hoping to just remove all and then start from scratch so I know the data is not fragmented on the drives. Thanks again... Nunzio ________________________________ From: Anirudh Sundar <sundar.anir...@gmail.com> To: Nunzio Daveri <nunziodav...@yahoo.com> Cc: mysql@lists.mysql.com Sent: Tue, August 10, 2010 1:06:41 AM Subject: Re: Dropping ALL indexes from a database / not just a table? Hello Nunzio, Instead of Dropping a index, you can disable the indexes and get the work done and re-enable them. If you are ok with this then run the below as a shell script :- MUSER="username" MPASS="password" DATABASE="dbname" for db in $DATABASE do echo "starting disabling indexes for database -- $db" echo "----------------------------------------------------------" TABLES=`mysql -u $MUSER -p$MPASS $db -e "show tables"` for table in $TABLES do mysql -u $MUSER -p$MPASS $db -e "Alter table $table disable keys" done echo "completed disabling indexes for database -- $db" done Cheers, Anirudh Sundar On Tue, Aug 10, 2010 at 1:33 AM, Nunzio Daveri <nunziodav...@yahoo.com> wrote: Hello Gurus, is there a way / script that will let me DROP ALL the indexes in a >single database? for example, lets say my database is call db_Animals, and >inside db_Animals there are 97 tables, is there a SINGLE command or a perl >script of some kind that can read all the MYI files, remove the .MYI from the >file name then proceed to deleting whatever indexes it finds? I am doing this >to debug a server that seems to be slow and sluggish. After I am done deleting >I will review the slow query logs and then re-index to get the best performance? > >TIA... > >Nunzio > > > >