List:General Discussion« Previous MessageNext Message »
From:Nunzio Daveri Date:August 10 2010 2:43pm
Subject:Re: Dropping ALL indexes from a database / not just a table?
View as plain text  
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.anirudh@stripped>
To: Nunzio Daveri <nunziodaveri@stripped>
Cc: mysql@stripped
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 <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