Tags: Database Index , MySQL

Database Index
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

As described above database index helps only for search operation. Insert, Update and Delete will suffer. Index should be created only selected fields based on your retrieval of data (Columns).

- Source (WikiPedia: http://en.wikipedia.org/wiki/Database_index)

/* It'll show all indexes except Primary Key */
SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = (SELECT DATABASE())
AND INDEX_NAME != 'PRIMARY';

DROP ALL TABLE INDEXES
Basic Syntax of dropping index is DROP INDEX INDEX_NAME ON TABLE_NAME; but to drop index in one single shot is not possible. You've to loop through all indexes of database. Thus, We've to use Stored Procedure in MySQL.

DROP PROCEDURE IF EXISTS usp_DropIndexOfTable;
DELIMITER $$
CREATE PROCEDURE usp_DropIndexOfTable(IN TableName VARCHAR(100))
BEGIN
 /**
  * @Created By: Vicky Thakor
  * @Created On: 6th March, 2014
  * This will delete all Indexes of table except Primary Key
  * 
  * @Test: CALL usp_DropIndexOfTable('user_master');
  */
 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE v_index varchar(200) DEFAULT "";
        DEClARE index_cursor CURSOR FOR 
        /* In below query remove TABLE_NAME criteria to remove all indexes */
        SELECT INDEX_NAME
  FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA = (SELECT DATABASE())
  AND TABLE_NAME = TableName
  AND INDEX_NAME != 'PRIMARY';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

 DECLARE exit handler FOR sqlexception
 BEGIN
 -- ERROR
 SHOW ERRORS LIMIT 1;
 ROLLBACK;
 END;

 DECLARE exit handler FOR sqlwarning
 BEGIN
 -- WARNING
 SHOW WARNINGS LIMIT 1;
 ROLLBACK;
 END;

 OPEN index_cursor; 
  get_index: LOOP
   FETCH index_cursor INTO v_index;

   IF v_finished = 1 THEN 
    LEAVE get_index;
   END IF;
  
   SET @v_query = CONCAT('DROP INDEX ',v_index,' ON ', TableName);
   PREPARE stmt FROM @v_query;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END LOOP get_index;
       CLOSE index_cursor;
END$$
DELIMITER ;

Just fire above Stored Procedure in your database and then execute this stored procedure using
CALL usp_DropIndexOfTable('user_master'). You can modify above stored procedure according to your requirement.

Tags: Database Index , MySQL