List:General Discussion« Previous MessageNext Message »
From:mos Date:August 10 2010 2:31pm
Subject:Re: Dropping ALL indexes from a database / not just a table?
View as plain text  
At 01:06 AM 8/10/2010, you wrote:
>Hello Nunzio,
>
>Instead of Dropping a index, you can disable the indexes and get the work
>done and re-enable them.

Disabling keys will NOT disable Primary or Unique keys. They will still be 
active.

Mike



>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 <nunziodaveri@stripped>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
> >
> >
> >
> >

Thread
Dropping ALL indexes from a database / not just a table?Nunzio Daveri9 Aug
  • Re: Dropping ALL indexes from a database / not just a table?Anirudh Sundar10 Aug
    • Re: Dropping ALL indexes from a database / not just a table?mos10 Aug
    • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri10 Aug
      • Re: Dropping ALL indexes from a database / not just a table?Michael Dykman10 Aug
        • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri10 Aug
          • Re: Dropping ALL indexes from a database / not just a table?Michael Dykman10 Aug
            • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri10 Aug
              • Re: Dropping ALL indexes from a database / not just a table?Michael Dykman11 Aug
                • Re: Dropping ALL indexes from a database / not just a table?Nunzio Daveri11 Aug