Question 205432 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Drop Index's with loop?

I have a database with a lot of unscary indexes. I don’t want to remove them all with one big query for time reasons. I’m not very familiar with query languages so is there a way to loop through the index list and remove all indexes with exception of PKs or is doing it the long way better/safer.

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks everyone for the helpful advice. I ended up taking Tadman's advice and used PHP to run a loop. here is the code in case anyone in the future has the same question/problem.

    --Query To get the index table of the table you wish to change
$sql = "SHOW INDEX FROM `Your_Table_Name`";
$Table = $conn->query($sql); 

--while loop to get the rows in the index table
while($Index=mysqli_fetch_array($Table)){
     $Name = $Index['Key_name']; --get name of index

     if($Name != 'PRIMARY'){ --see if index is primary Key
        -- if not run query to remove index
        $sql = "ALTER TABLE `Your_Table_Name` DROP INDEX `$Name`"; 
        $conn->query($sql);
     }
 }


  2. 0

    There are no looping constructs for DDL (DROP INDEX etc). You could dynamically build DDL statements and execute them, but depending on how much you have to drop, it’s probably not worth the bother.

    You say "I don’t want to remove them all with one big query for time reasons." but "remove them all with one big query" is what you would be doing if you looped through a list of indexes and executed the DROP for each.

    Login or Signup to reply.
  3. 0

    If you have the grants to access to information_schema, you can generate a procedure that gets index names from specific table (INNODB_SYS_INDEXES), create the drop statement (with PREPARE command) and run it.

    In the INNODB_SYS_INDEXES the Indexes have a specific flag (TYPE field) [0 (secondary index), 1 (cluster index), 2 (unique index), 3 (primary key), 32 (full-text index).)

    bye

    Login or Signup to reply.
  4. 0

    You have to create a procedure (my_procedure) with the following declarations:

    DECLARE not_found INT DEFAULT FALSE;
DECLARE v_TABLE_SCHEMA, v_TABLE_NAME, v_INDEX_NAME TEXT;

CREATE cur_indexes CURSOR FOR
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, i.NAME
FROM information_schema.TABLES t 
JOIN information_schema.INNODB_SYS_TABLES tt ON (tt.NAME = concat(t.TABLE_SCHEMA,'/',t.TABLE_NAME))
JOIN information_schema.INNODB_SYS_INDEXES i ON (tt.TABLE_ID = i.TABLE_ID)
WHERE t.TABLE_SCHEMA = [your_schema]
AND i.TYPE = [index type you need to delete];

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = TRUE;

    (To do this you must have the right privileges on information_schema).

    Now you can OPEN the cursor and FETCH it into three variables:
    v_TABLE_SCHEMA, v_TABLE_NAME, v_INDEX_NAME that you have previously declared;

    OPEN cur_indexes;
        get_data: LOOP       
        FETCH cur_indexes INTO v_TABLE_SCHEMA, v_TABLE_NAME, v_INDEX_NAME;
        IF not_found = TRUE THEN 
             LEAVE get_data;
        END IF;

    for each entry of the cursor you have to create a proper statement:

    SET @query := concat('ALTER TABLE ',v_TABLE_SCHEMA,'.',v_TABLE_NAME,' DROP INDEX ',v_INDEX_NAME);

    Then you have to run the statement contained into the string with the following sequence of statements:

    PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

    then close the loop.

        END LOOP get_data;          
CLOSE cur_indexes;

    now you can run

    CALL my_procedure();

    to drop in few seconds all indexes.

    ***** be careful before running the procedure!
    run the select that is used in the creation of the cursor to make you sure that the index that you are going to select are really to delete.
    Once runned the procedure there is no way to roll back.

    good luck!
    bye

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search