MySQL dropping all indexes from table

Question

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?

Answer 1

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.

Answer 2

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`

Answer 3

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`;

Answer 4

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