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
To: Nunzio Daveri
Cc: Anirudh Sundar ; 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 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
> To: Nunzio Daveri
> Cc: Anirudh Sundar ; 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
> 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 e
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 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
> To: Nunzio Daveri
> Cc: Anirudh Sundar ; 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
> 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
>> To: Nunzio Daveri
>> Cc: Anirudh Sundar ; 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,
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
To: Nunzio Daveri
Cc: Anirudh Sundar ; 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 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
> To: Nunzio Daveri
> Cc: Anirudh Sundar ; 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='';
>
> - md
>
> On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri
> 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
&
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 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
> To: Nunzio Daveri
> Cc: Anirudh Sundar ; 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='';
>
> - md
>
> On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri
> 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
>> To: Nunzio Daveri
>> 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
>> 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?
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
To: Nunzio Daveri
Cc: Anirudh Sundar ; 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='';
- md
On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri 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
> To: Nunzio Daveri
> 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 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?
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='';
- md
On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri 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
> To: Nunzio Daveri
> 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 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?
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 To: Nunzio Daveri 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 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?
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 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 > > > > -- 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 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 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 > > > >
Dropping ALL indexes from a database / not just a table?
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
