Re: Dropping ALL indexes from a database / not just a table?
How does one do that? How do you drop auto-increment attribute then drop the index then restart the auto increment value where it was before you dropped it?? I did not know you could do that. The reason I ask is because the dbf_UID is a unique id tag the coders use to identify a product by manufacturer kinda like a upc code for their internal db. Can't have dups and don't want to have non-used id's in the db. Any help, direction is much appreciated. 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 5:03:44 PM Subject: Re: Dropping ALL indexes from a database / not just a table? 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 nunziodav...@yahoo.com wrote: 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
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.comwrote: 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
Re: Dropping ALL indexes from a database / not just a table?
At 01:06 AM 8/10/2010, you wrote: Hello Nunzio, Instead of Dropping a index, you can disable the indexes and get the work done and re-enable them. Disabling keys will NOT disable Primary or Unique keys. They will still be active. Mike 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.comwrote: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Dropping ALL indexes from a database / not just a table?
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
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Dropping ALL indexes from a database / not just a table?
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.
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Dropping ALL indexes from a database / not just a table?
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 /
Re: Dropping ALL indexes from a database / not just a table?
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 nunziodav...@yahoo.com wrote: 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