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 <[email protected]>
To: Nunzio Daveri <[email protected]>
Cc: Anirudh Sundar <[email protected]>; [email protected]
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 <[email protected]> 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 <[email protected]>
> To: Nunzio Daveri <[email protected]>
> Cc: [email protected]
> 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 <[email protected]> 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
- [email protected]
May the Source be with you.
