Geek asks and answers

MySQL command to delete all non-primary table indexes

Is there a MySQL command that can remove all additional indexes except the first in one table, thereby preserving only the main index?

I can remove the second Post_Date index using the following MySQL command, but I have problems with the rest of the rest.

mysql_query("ALTER TABLE $TableName DROP INDEX Post_Date");

Additional Post_Date and Post_Date_x indexes are created at the beginning of the script , so I want to delete them at the end of the script using the MySQL command at the end of the script.

Keep in mind that _x is in Post_Date_x and varies and can go from 1 to 10 or from 1 to 100. Therefore, you may need a loop or IF statement.

MySQL command will be part of a PHP script

Thank you for your time.

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  id  830 A       
 Edit    Drop   Post_Date   BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_2 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_3 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_4 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_5 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_6 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_7 BTREE   Yes No  Post_Date   830 A       
 Edit    Drop   Post_Date_8 BTREE   Yes No  Post_Date   830 A
+3
source share
2 answers

That should do it -

$res = mysql_query("SHOW INDEX FROM `$TableName` WHERE `Key_name` LIKE 'Post_Date%'");

while ($row = mysql_fetch_object($res)) {
    mysql_query("DROP INDEX `{$row->Key_name}` ON `{$row->Table}`") or die(mysql_error());
}
+4
source

Sorry, but there is no MySQL command for ALTER TABLE ny_table DROP ALL INDEXES EXCEPT PRIMARY KEY.

You would need to create a specific name for each index as it is created, and then create a loop to individually run the ALTER TABLE ... DROP INDEXSQL command for each index using the same names.

Note also that creating indexes is quite expensive - if you don't make> ~ 200 index-optimized queries, creating and deleting an index probably takes longer than when using it.

0
source

More articles:


All Articles