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

It's not a completely solution and will need some tweaking..  You
might have to run the PRIMARY KEYS distinctly from the rest.

 - michael dykman


On Tue, Aug 10, 2010 at 4:43 PM, Nunzio Daveri <nunziodaveri@stripped> wro=
te:
> Hello Michael, thanks for the one liner.=A0 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 r=
an
> 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.=A0 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;=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |
> | ALTER TABLE dbt_Veggies DROP INDEX PRIMARY;=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |
> .
> .
> .
> | ALTER TABLE dbt_Logs DROP INDEX
> PRIMARY;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |
> +------------------------------------------------------------------------=
-+
> 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 manua=
l
> 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 manua=
l
> 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 <mdykman@stripped>
> To: Nunzio Daveri <nunziodaveri@stripped>
> Cc: Anirudh Sundar <sundar.anirudh@stripped>; mysql@stripped
> 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=3D'<mydatabase>';
>
> - md
>
> On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri <nunziodaveri@stripped>
> wrote:
>> 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
>> 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 th=
e
>> 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 wor=
k
>> 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>
>> wrote:
>>
>> Hello Gurus, is there a way / script that will let me DROP ALL the index=
es
>> 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 pe=
rl
>>>script of some kind that can read all the MYI files, remove the .MYI fro=
m
>>> the
>>>file name then proceed to deleting whatever indexes it finds? =A0I am do=
ing
>>> 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
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
> --
> =A0- michael dykman
> =A0- mdykman@stripped
>
> =A0May the Source be with you.
>
>



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

=A0May the Source be with you.