List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:December 3 2007 6:26pm
Subject:Re: how to "drop index if exists"
View as plain text  
I actually suggested that last night, and thought better of it because
the alter ignore was so much simpler...

On Dec 3, 2007 11:18 AM, Rolando Edwards <redwards@stripped> wrote:
> You may want to check to see if the index exists first.
> Just query the table INFORMATION_SCHEMA.STATISTICS:
>
> SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
> WHERE table_schema = '<given schema>'
> AND table_name = '<given table name>'
> AND index_name = '<given index name>';
>
> This returns the number of columns the index contains.
>
> If this query returns zero(0), then the index does not exist.
>
> If this query returns a positive number, then call
> ALTER TABLE <tbl-name> DROP INDEX <index-name>;
>
> You may want to write this stored procedure to do this.
> Here is the Code (change the 'util' schema to the schema you want) :
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `util`.`sp_DropIndex` $$
> CREATE PROCEDURE `util`.`sp_DropIndex` (tblSchema VARCHAR(64),tblName
> VARCHAR(64),ndxName VARCHAR(64))
> BEGIN
>
>     DECLARE IndexColumnCount INT;
>     DECLARE SQLStatement VARCHAR(256);
>
>     SELECT COUNT(1) INTO IndexColumnCount
>     FROM information_schema.statistics
>     WHERE table_schema = tblSchema
>     AND table_name = tblName
>     AND index_name = ndxName;
>
>     IF IndexColumnCount > 0 THEN
>         SET SQLStatement = CONCAT('ALTER TABLE `',tblSchema,'`.`',tblName,'` DROP
> INDEX `',ndxName,'`');
>         SET @SQLStmt = SQLStatement;
>         PREPARE s FROM @SQLStmt;
>         EXECUTE s;
>         DEALLOCATE PREPARE s;
>     END IF;
>
> END $$
>
> DELIMITER ;
>
> Give it a try !!!
>
>
> -----Original Message-----
> From: Rob Wultsch [mailto:wultsch@stripped]
> Sent: Monday, December 03, 2007 12:51 PM
> To: Adam Lipscombe
> Cc: mysql@stripped
> Subject: Re: how to "drop index if exists"
>
> On Nov 29, 2007 4:34 AM, Adam Lipscombe <adam.lipscombe@stripped> wrote:
> > Folks
> >
> >
> > How can one conditionally drop an index in MySQL?
> >
> > Googling shows that the "drop index" does not support an "if exists" qualifier -
> apparently a bug
> > has been raised but as far as I know its not fixed yet.
> >
> > Does anyone know of a work-around?
> >
> > TIA - Adam
> >
>
> Sent my first response late at night and not the community... And the
> response  also sucked.
> DROP INDEX is mapped to ALTER TABLE tbl_name
>  DROP INDEX.
> ALTER IGNORE TABLE tbl_name
>  DROP INDEX ought to be functionally equivalent to IF EXISTS. What is your goal?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>



-- 
Rob Wultsch
(480)223-2566
wultsch@stripped (email/google im)
wultsch (aim)
wultsch@stripped (msn)
Thread
how to "drop index if exists"Adam Lipscombe29 Nov
  • Re: how to "drop index if exists"Rob Wultsch3 Dec
    • RE: how to "drop index if exists"Rolando Edwards3 Dec
      • Re: how to "drop index if exists"Rob Wultsch3 Dec
    • Re: how to "drop index if exists"Adam Lipscombe5 Dec
    • Re: how to "drop index if exists"Adam Lipscombe5 Dec
      • Re: how to "drop index if exists"Baron Schwartz5 Dec