List:General Discussion« Previous MessageNext Message »
From:John Thorpe Date:March 30 2004 9:48pm
Subject:Re: Drop all keys / indexes on a table?
View as plain text  
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:pwr@stripped] 
>>Sent: Tuesday, March 30, 2004 11:55 AM
>>To: Daevid Vincent; mysql@stripped
>>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" <daevid@stripped>
>>To: <mysql@stripped>
>>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:victoria.reznichenko@stripped]
>>
>>>>Sent: Tuesday, March 30, 2004 12:48 AM
>>>>To: mysql@stripped
>>>>Subject: Re: Drop all keys / indexes on a table?
>>>>
>>>>"Daevid Vincent" <daevid@stripped> 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
>>>> / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
>>>>/_/  /_/\_, /___/\___\_\___/   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/mysql?unsub=1
>>>>
>>>
>>>
>>>-- 
>>>MySQL General Mailing List
>>>For list archives: http://lists.mysql.com/mysql
>>>To unsubscribe:    
>>
>>http://lists.mysql.com/mysql?unsub=1
>>
>>>
> 
> 

Thread
Drop all keys / indexes on a table?Daevid Vincent29 Mar
  • Re: Drop all keys / indexes on a table?Victoria Reznichenko30 Mar
    • RE: Drop all keys / indexes on a table?Daevid Vincent30 Mar
      • Re: Drop all keys / indexes on a table?PeterWR30 Mar
        • RE: Drop all keys / indexes on a table?Daevid Vincent30 Mar
          • Re: Drop all keys / indexes on a table?John Thorpe30 Mar
            • RE: Drop all keys / indexes on a table?Daevid Vincent31 Mar
      • using result as a fieldMojtaba Faridzad30 Mar
      • RE: Drop all keys / indexes on a table?Daevid Vincent30 Mar