Hi Micheal and all, ok so I did some digging around and I still can't find why I cant drop the last few indexes.
mysql> SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = 'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY'; +----------+ | COUNT(1) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> DESCRIBE dbt_Fruit; +------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------+----------------+ | dbf_UID | int(10) unsigned | NO | PRI | NULL | auto_increment | | dbf_Vendor | varchar(30) | NO | | | | | dbf_Code | varchar(30) | NO | | | | | dbf_Notes | text | YES | | NULL | | +------------------+------------------+------+-----+---------+----------------+ mysql> ALTER TABLE dbt_Fruit DROP FOREIGN KEY dbf_UID; Query OK, 2947 rows affected (0.05 sec) Records: 2947 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE dbt_Fruit DROP PRIMARY KEY; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> ALTER TABLE dbt_Fruit DROP 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 '' at line 1 Any ideas??? I am wondering if it has something to do with the fact that dbf_UID is a primary AND auto_increment? TIA... Nunzio ________________________________ From: Michael Dykman <mdyk...@gmail.com> To: Nunzio Daveri <nunziodav...@yahoo.com> Cc: Anirudh Sundar <sundar.anir...@gmail.com>; mysql@lists.mysql.com Sent: Tue, August 10, 2010 4:10:37 PM Subject: Re: Dropping ALL indexes from a database / not just a table? 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 <nunziodav...@yahoo.com> wrote: > 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 <mdyk...@gmail.com> > To: Nunzio Daveri <nunziodav...@yahoo.com> > Cc: Anirudh Sundar <sundar.anir...@gmail.com>; mysql@lists.mysql.com > 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 <nunziodav...@yahoo.com> > 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 <sundar.anir...@gmail.com> >> To: Nunzio Daveri <nunziodav...@yahoo.com> >> Cc: mysql@lists.mysql.com >> 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 <nunziodav...@yahoo.com> >> 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 > - mdyk...@gmail.com > > May the Source be with you. > > -- - michael dykman - mdyk...@gmail.com May the Source be with you.