CODEWITHSUNDEEP
mysqlindexingalter-table
aviv
aviv

Reputation: 2809

MySQL dropping all indexes from table

I have a MySQL database that runs for some time now with many changes on it. Lately I looked over it and I noticed that in some cases I have doubled the index on the same field. Some Indexes are missing, and in general there is a huge mess in all the indexes.

I wants to drop all indexes from a table. Later on I have a prepared script that will run ALTER TABLE and add the relevant indexes.

Is there a way to drop all indexes from a table?

Upvotes: 20

Views: 51788

Answers (4)

Fernando Kosh
Fernando Kosh

Reputation: 3593

In Ruby on Rails I do this:

indexes = ActiveRecord::Base.connection.execute("SHOW INDEX FROM tablename")
indexes.each do |index|
  ActiveRecord::Base.connection.execute("ALTER TABLE tablename DROP INDEX #{index[2]};")
end

Upvotes: 7

Vojtech Kurka
Vojtech Kurka

Reputation: 905

Simple script: 

-- list all non-unique indexes
SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE NON_UNIQUE = 1 AND table_schema = 'mydatabase' AND table_name = 'mytable'
GROUP BY 1,2;

-- drop all non-unique indexes
SET SESSION group_concat_max_len=10240;

SELECT CONCAT('ALTER TABLE ', `Table`, ' DROP INDEX ', GROUP_CONCAT(`Index` SEPARATOR ', DROP INDEX '),';' )
FROM (
SELECT table_name AS `Table`,
       index_name AS `Index`
FROM information_schema.statistics
WHERE NON_UNIQUE = 1 AND table_schema = 'mydatabase' AND table_name = 'mytable'
GROUP BY `Table`, `Index`) AS tmp
GROUP BY `Table`;

-- add all non-unique indexes , WITHOUT index length spec
SET SESSION group_concat_max_len=10240;
SELECT CONCAT('ALTER TABLE ', `Table`, ' ADD INDEX ', GROUP_CONCAT(CONCAT(`Index`, '(', `Columns`, ')') SEPARATOR ',\n ADD INDEX ') )
FROM (
SELECT table_name AS `Table`,
       index_name AS `Index`,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE NON_UNIQUE = 1 AND table_schema = 'mydatabase' AND table_name = 'mytable'
GROUP BY `Table`, `Index`) AS tmp
GROUP BY `Table`;

Upvotes: 28

The Surrican
The Surrican

Reputation: 29856

no there isnt a command. you can however write a script that shows all databases, shows all tables inside thowe databases, shows all indexes inside those tables and drops them all. but i'n not gonna write that for you if you don't start accepting some answers. you can also use phpmyadmin or another graphical tool to select this neat "check all" box for every table.

Upvotes: 4

Loïc Février
Loïc Février

Reputation: 7750

If you have phpmyadmin or any similar tool you can do that very easily graphically.

Or for every index do something like

ALTER TABLE  `table` DROP INDEX  `NameIndex`

You can get the indexes with

SHOW INDEX FROM `table`

Upvotes: 16

Related Questions