Re: Drop all keys / indexes on a table?

[John Thorpe]

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(NR>1) 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]