Re: Drop all keys / indexes on a table?
Daevid Vincent [EMAIL PROTECTED] wrote: It has come to my attention that we have maxed out our keys due to a stupid update script bug. It seemst that we've not been explicitly naming our keys and therefore mysql tried to be helpful and adds a new key each time! *sigh*. Is there a SQL command to DROP ALL keys on a table, so I can just ALTER it and add them specifically again? Specify several DROP INDEX clause in the single ALTER TABLE statement: ALTER TABLE table_name DROP INDEX index_name1, DROP INDEX index_name2, .. , DROP INDEX index_nameN; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Drop all keys / indexes on a table?
Ugh. I was afraid you were going to say that... Seriously, there's no way to just 'wildcard' ALL indexes, someone should add that as a feature request. We're using 4.0.17 BTW. What happens if I list out all the indexes that there _could_ be in one ALTER line like that, and one of the indexes doesn't actually exist? Will the whole ALTER fail? Here's the situation, I wrote a script that runs recursively through a directory and applies all the .sql files it finds (in alpha order). This script runs as part of a client update, and doesn't necessarily run the same number of times for everyone. So, some clients may have extra indexes: foo_1, foo_2, foo_3, ... foo_15 etc. (the problem at hand), and other clients may just have: foo_1, foo_2, foo_3. -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 12:48 AM To: [EMAIL PROTECTED] Subject: Re: Drop all keys / indexes on a table? Daevid Vincent [EMAIL PROTECTED] wrote: It has come to my attention that we have maxed out our keys due to a stupid update script bug. It seemst that we've not been explicitly naming our keys and therefore mysql tried to be helpful and adds a new key each time! *sigh*. Is there a SQL command to DROP ALL keys on a table, so I can just ALTER it and add them specifically again? Specify several DROP INDEX clause in the single ALTER TABLE statement: ALTER TABLE table_name DROP INDEX index_name1, DROP INDEX index_name2, .. , DROP INDEX index_nameN; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop all keys / indexes on a table?
Hi, Take at look at CHECK TABEL - as far as I remember, the CHECK TABLE EXTENDED will do a re-index (check index), otherwise some of the other OPTIMIZE, etc. can help on this. Take a look in the exellent manual. Best regards Peter - Original Message - From: Daevid Vincent [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 9:44 PM Subject: RE: Drop all keys / indexes on a table? Ugh. I was afraid you were going to say that... Seriously, there's no way to just 'wildcard' ALL indexes, someone should add that as a feature request. We're using 4.0.17 BTW. What happens if I list out all the indexes that there _could_ be in one ALTER line like that, and one of the indexes doesn't actually exist? Will the whole ALTER fail? Here's the situation, I wrote a script that runs recursively through a directory and applies all the .sql files it finds (in alpha order). This script runs as part of a client update, and doesn't necessarily run the same number of times for everyone. So, some clients may have extra indexes: foo_1, foo_2, foo_3, ... foo_15 etc. (the problem at hand), and other clients may just have: foo_1, foo_2, foo_3. -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 12:48 AM To: [EMAIL PROTECTED] Subject: Re: Drop all keys / indexes on a table? Daevid Vincent [EMAIL PROTECTED] wrote: It has come to my attention that we have maxed out our keys due to a stupid update script bug. It seemst that we've not been explicitly naming our keys and therefore mysql tried to be helpful and adds a new key each time! *sigh*. Is there a SQL command to DROP ALL keys on a table, so I can just ALTER it and add them specifically again? Specify several DROP INDEX clause in the single ALTER TABLE statement: ALTER TABLE table_name DROP INDEX index_name1, DROP INDEX index_name2, .. , DROP INDEX index_nameN; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Drop all keys / indexes on a table?
Thanks for the reply, however looking at all those options and none seems to do what I need. -Original Message- From: PeterWR [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 11:55 AM To: Daevid Vincent; [EMAIL PROTECTED] Subject: Re: Drop all keys / indexes on a table? Hi, Take at look at CHECK TABEL - as far as I remember, the CHECK TABLE EXTENDED will do a re-index (check index), otherwise some of the other OPTIMIZE, etc. can help on this. Take a look in the exellent manual. Best regards Peter - Original Message - From: Daevid Vincent [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 9:44 PM Subject: RE: Drop all keys / indexes on a table? Ugh. I was afraid you were going to say that... Seriously, there's no way to just 'wildcard' ALL indexes, someone should add that as a feature request. We're using 4.0.17 BTW. What happens if I list out all the indexes that there _could_ be in one ALTER line like that, and one of the indexes doesn't actually exist? Will the whole ALTER fail? Here's the situation, I wrote a script that runs recursively through a directory and applies all the .sql files it finds (in alpha order). This script runs as part of a client update, and doesn't necessarily run the same number of times for everyone. So, some clients may have extra indexes: foo_1, foo_2, foo_3, ... foo_15 etc. (the problem at hand), and other clients may just have: foo_1, foo_2, foo_3. -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 30, 2004 12:48 AM To: [EMAIL PROTECTED] Subject: Re: Drop all keys / indexes on a table? Daevid Vincent [EMAIL PROTECTED] wrote: It has come to my attention that we have maxed out our keys due to a stupid update script bug. It seemst that we've not been explicitly naming our keys and therefore mysql tried to be helpful and adds a new key each time! *sigh*. Is there a SQL command to DROP ALL keys on a table, so I can just ALTER it and add them specifically again? Specify several DROP INDEX clause in the single ALTER TABLE statement: ALTER TABLE table_name DROP INDEX index_name1, DROP INDEX index_name2, .. , DROP INDEX index_nameN; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop all keys / indexes on a table?
I've been using comand line piping through awk to handle
mass tables modifications and listings. E.g. in your case
something like the following would hit every index, except
the primary keys, in table tablename in the test database.
echo show index from tablename |
| mysql -uuser -ppswd test
| awk '{if($3 !~ /Key_name/ $3 !~ /PRIMARY/) print $1 $3}'
| awk '{print alter table $1 drop index $2;}'
| mysql -uuser -ppswd test
To hit every table in the test database:
mysql -uuser -ppswd -e show tables from test
| awk '{if(NR1) print show index from $1;}'
| mysql -uuser -ppswd test
| awk '{if($3 !~ /Key_name/ $3 !~ /PRIMARY/) print $1 $3}'
| awk '{print alter table $1 drop index $2;}'
| mysql -uuser -ppswd test
John
Daevid Vincent wrote:
Thanks for the reply, however looking at all those options and none seems to
do what I need.
-Original Message-
From: PeterWR [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 30, 2004 11:55 AM
To: Daevid Vincent; [EMAIL PROTECTED]
Subject: Re: Drop all keys / indexes on a table?
Hi,
Take at look at CHECK TABEL - as far as I remember, the CHECK
TABLE EXTENDED
will do a re-index (check index), otherwise some of the other
OPTIMIZE, etc.
can help on this.
Take a look in the exellent manual.
Best regards
Peter
- Original Message -
From: Daevid Vincent [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 30, 2004 9:44 PM
Subject: RE: Drop all keys / indexes on a table?
Ugh. I was afraid you were going to say that...
Seriously, there's no way to just 'wildcard' ALL indexes,
someone should
add
that as a feature request. We're using 4.0.17 BTW.
What happens if I list out all the indexes that there
_could_ be in one
ALTER line like that, and one of the indexes doesn't
actually exist? Will
the whole ALTER fail?
Here's the situation, I wrote a script that runs
recursively through a
directory and applies all the .sql files it finds (in alpha
order). This
script runs as part of a client update, and doesn't
necessarily run the
same
number of times for everyone. So, some clients may have
extra indexes:
foo_1, foo_2, foo_3, ... foo_15 etc. (the problem at hand),
and other
clients may just have: foo_1, foo_2, foo_3.
-Original Message-
From: Victoria Reznichenko
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 30, 2004 12:48 AM
To: [EMAIL PROTECTED]
Subject: Re: Drop all keys / indexes on a table?
Daevid Vincent [EMAIL PROTECTED] wrote:
It has come to my attention that we have maxed out our keys
due to a stupid
update script bug. It seemst that we've not been explicitly
naming our keys
and therefore mysql tried to be helpful and adds a new key
each time!
*sigh*.
Is there a SQL command to DROP ALL keys on a table, so I
can just ALTER it
and add them specifically again?
Specify several DROP INDEX clause in the single ALTER
TABLE statement:
ALTER TABLE table_name DROP INDEX index_name1, DROP
INDEX index_name2, .. , DROP INDEX index_nameN;
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Drop all keys / indexes on a table?
And the answer to this is, YES, the whole ALTER query FAILS and NONE of the indicies are dropped. *sigh* mysql ALTER TABLE poop DROP INDEX name_2, DROP INDEX name_3, DROP INDEX name_4; ERROR 1091: Can't DROP 'name_4'. Check that column/key exists -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] What happens if I list out all the indexes that there _could_ be in one ALTER line like that, and one of the indexes doesn't actually exist? Will the whole ALTER fail? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Drop all keys / indexes on a table?
John, you are my father!
I've taken your script and tweaked it a bit more:
SNIP -
#!/bin/sh
DBLIST=mydb1 mydb2 mytest mytestdb
USER=uzer
PASSWORD=passwerd
clear
for DB in $DBLIST
do
echo Removing all indexes (not primary) in Database: '$DB'
TABLES=$(mysql -u$USER -p$PASSWORD --force -e SHOW TABLES FROM $DB
\
| awk '{if(NR1) print $1;}')
for t in $TABLES;
do
echo -e \tFixing: '$t'
echo SHOW INDEX FROM $t \
| mysql -u$USER -p$PASSWORD --force $DB \
| awk '{if($3 !~ /Key_name/ $3 !~ /PRIMARY/) print $1
$3}' \
| awk '{print ALTER TABLE $1 DROP INDEX $2;}' \
| mysql -u$USER -p$PASSWORD --force $DB
done
done
-Original Message-
From: John Thorpe [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 30, 2004 1:49 PM
To: Daevid Vincent
Cc: [EMAIL PROTECTED]
Subject: Re: Drop all keys / indexes on a table?
I've been using comand line piping through awk to handle
mass tables modifications and listings. E.g. in your case
something like the following would hit every index, except
the primary keys, in table tablename in the test database.
echo show index from tablename |
| mysql -uuser -ppswd test
| awk '{if($3 !~ /Key_name/ $3 !~ /PRIMARY/) print $1 $3}'
| awk '{print alter table $1 drop index $2;}'
| mysql -uuser -ppswd test
To hit every table in the test database:
mysql -uuser -ppswd -e show tables from test
| awk '{if(NR1) print show index from $1;}'
| mysql -uuser -ppswd test
| awk '{if($3 !~ /Key_name/ $3 !~ /PRIMARY/) print $1 $3}'
| awk '{print alter table $1 drop index $2;}'
| mysql -uuser -ppswd test
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Drop all keys / indexes on a table?
It has come to my attention that we have maxed out our keys due to a stupid update script bug. It seemst that we've not been explicitly naming our keys and therefore mysql tried to be helpful and adds a new key each time! *sigh*. Is there a SQL command to DROP ALL keys on a table, so I can just ALTER it and add them specifically again? http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
