Hello Michael, thanks for the one liner. I ran it BUT I started to get errors after I ran it the first time, this is what I got the 2nd time I ran it (first time I ran it I had 63 rows in the query, the 2nd time I have 9). I ran it twice to make sure it got rid of the indexed. I verified the index size dropped from 850 mb to 65 mb.
+-------------------------------------------------------------------------+ | concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';') | +-------------------------------------------------------------------------+ | ALTER TABLE dbt_Fruits DROP INDEX PRIMARY; | | ALTER TABLE dbt_Veggies DROP INDEX PRIMARY; | . . . | ALTER TABLE dbt_Logs DROP INDEX PRIMARY; | +-------------------------------------------------------------------------+ 9 rows in set (0.01 sec) mysql> ALTER TABLE dbt_Fruits DROP INDEX PRIMARY; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY' at line 1 mysql> ALTER TABLE dbt_Logs DROP INDEX PRIMARY; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY' at line 1 mysql> Thanks again... Nunzio ________________________________ From: Michael Dykman <mdyk...@gmail.com> To: Nunzio Daveri <nunziodav...@yahoo.com> Cc: Anirudh Sundar <sundar.anir...@gmail.com>; mysql@lists.mysql.com Sent: Tue, August 10, 2010 3:17:48 PM Subject: Re: Dropping ALL indexes from a database / not just a table? This should give you a good starting point (not tested): select distinct concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';') from information_schema.key_column_usage where TABLE_SCHEMA='<mydatabase>'; - md On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri <nunziodav...@yahoo.com> wrote: > 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 >> >> >> >> > > > > -- - michael dykman - mdyk...@gmail.com May the Source be with you.