From: Michael Dykman Date: August 10 2010 10:03pm Subject: Re: Dropping ALL indexes from a database / not just a table? List-Archive: http://lists.mysql.com/mysql/222484 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable auto_increment is only allowed on primary-keyed columns. I expect it is not allowing you to drop the primary key because that column has the auto_increment attribute. Drop that manually, and the primary key should be able to let go. - md On Tue, Aug 10, 2010 at 5:58 PM, Nunzio Daveri wro= te: > Hi Micheal and all, ok so I did some digging around and I still can't fin= d > why I cant drop the last few indexes. > > mysql> SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_sch= ema > =3D 'db_Market' AND table_name =3D 'dbt_Fruit' and index_name =3D 'PRIMAR= Y'; > +----------+ > | COUNT(1) | > +----------+ > |=A0=A0=A0=A0=A0=A0=A0 1 | > +----------+ > 1 row in set (0.00 sec) > > mysql> DESCRIBE dbt_Fruit; > +------------------+------------------+------+-----+---------+-----------= -----+ > | Field=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | Type=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 | Null | Key | Default | > Extra=A0=A0=A0=A0=A0=A0=A0=A0=A0 | > +------------------+------------------+------+-----+---------+-----------= -----+ > | dbf_UID=A0=A0=A0=A0=A0=A0=A0=A0=A0 | int(10) unsigned | NO=A0=A0 | PRI = | NULL=A0=A0=A0 | > auto_increment | > | dbf_Vendor=A0=A0=A0=A0=A0=A0 | varchar(30)=A0=A0=A0=A0=A0 | NO=A0=A0 |= =A0=A0=A0=A0 | > |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | > | dbf_Code=A0=A0=A0=A0=A0=A0=A0=A0 | varchar(30)=A0=A0=A0=A0=A0 | NO=A0= =A0 |=A0=A0=A0=A0 | > |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | > | dbf_Notes=A0=A0=A0=A0=A0=A0=A0 | text=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 | YES=A0 |=A0=A0=A0=A0 | NULL > |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | > +------------------+------------------+------+-----+---------+-----------= -----+ > > mysql> ALTER TABLE dbt_Fruit DROP FOREIGN KEY dbf_UID; > Query OK, 2947 rows affected (0.05 sec) > Records: 2947=A0 Duplicates: 0=A0 Warnings: 0 > > mysql> ALTER TABLE dbt_Fruit DROP PRIMARY KEY; > ERROR 1075 (42000): Incorrect table definition; there can be only one aut= o > 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 manua= l > that corresponds to your MySQL server version for the right syntax to use > near '' at line 1 > > > Any ideas???=A0 I am wondering if it has something to do with the fact th= at > dbf_UID is a primary AND auto_increment? > > TIA... > > Nunzio > > > > ________________________________ > From: Michael Dykman > To: Nunzio Daveri > Cc: Anirudh Sundar ; mysql@stripped > 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..=A0 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 > wrote: >> Hello Michael, thanks for the one liner.=A0 I ran it BUT I started to ge= t >> 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.=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; >> | >> . >> . >> . >> | 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 manu= al >> that corresponds to your MySQL server version for the right syntax to us= e >> 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 manu= al >> that corresponds to your MySQL server version for the right syntax to us= e >> near 'PRIMARY' at line 1 >> mysql> >> >> Thanks again... >> >> Nunzio >> ________________________________ >> From: Michael Dykman >> To: Nunzio Daveri >> Cc: Anirudh Sundar ; 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''; >> >> - md >> >> On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri >> 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 t= he >>> data >>> is not fragmented on the drives. >>> >>> Thanks again... >>> >>> Nunzio >>> >>> >>> >>> >>> ________________________________ >>> From: Anirudh Sundar >>> To: Nunzio Daveri >>> 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 wo= rk >>> 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 >>> wrote: >>> >>> Hello Gurus, is there a way / script that will let me DROP ALL the >>> indexes >>> in a >>>>single database? =A0for example, lets say my database is call db_Animal= s, >>>> 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 fr= om >>>> the >>>>file name then proceed to deleting whatever indexes it finds? =A0I am d= oing >>>> this >>>>to debug a server that seems to be slow and sluggish. =A0After I am don= e >>>> 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. >> >> > > > > -- > =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.