From: Michael Dykman
Date: August 10 2010 8:17pm
Subject: Re: Dropping ALL indexes from a database / not just a table?
List-Archive: http://lists.mysql.com/mysql/222479
Message-Id: <AANLkTi=AHzy2SBiwA4ZaABbxbM=UOuQh6AM6pEfTsATz@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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=3D'<mydatabase>';

 - md

On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri <nunziodaveri@stripped> wr=
ote:
> Thanks for the feedback. =A0What I am trying to do is two things:
>
> 1. Remove all indexes and make the database smaller to copy and move to a=
nother
> 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 m=
ysql to
> recreate them and apparently it lessen data fragmentation if it starts fr=
om
> 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=3D"username"
> MPASS=3D"password"
> DATABASE=3D"dbname"
>
> for db in $DATABASE
> do
> =A0echo "starting disabling indexes for database -- $db"
> echo "----------------------------------------------------------"
> TABLES=3D`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> w=
rote:
>
> Hello Gurus, is there a way / script that will let me DROP ALL the indexe=
s in a
>>single database? =A0for example, lets say my database is call db_Animals,=
 and
>>inside db_Animals there are 97 tables, is there a SINGLE command or a per=
l
>>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? =A0I am doi=
ng this
>>to debug a server that seems to be slow and sluggish. =A0After I am done =
deleting
>>I will review the slow query logs and then re-index to get the best
> performance?
>>
>>TIA...
>>
>>Nunzio
>>
>>
>>
>>
>
>
>
>



--=20
=A0- michael dykman
=A0- mdykman@stripped

=A0May the Source be with you.