Disabling index in Mysql


ALTER TABLE ‘table-name’ ENABLE KEYS; — It will enable keys for TABLE
ALTER TABLE ‘table-name’ DISABLE KEYS; — It will disable keys for TABLE

Run following Query:

select * from information_schema.statistics where TABLE_SCHEMA=’db_name’ and TABLE_NAME=’table-name’;

-It gives the list of indexes on selected Database with table_name,index_name,index_type,comment etc.

-Check ‘Comment’ column it will show you status enable/disable for keys.

Note: FOR enable status ‘Comment’ column value is blank, that means key is enabled.

DISABLE/ENABLE index useful at the time of bulk insert to faster execution of insert statements.


1.First disable the Index index <> on table.

alter table disable KEYS;


show warnings;


SET autocommit=0;

show variables;
show variables like “unique_checks”

SET unique_checks=0;

show keys in

SET foreign_key_checks=0;

select * from information_schema.statistics where TABLE_SCHEMA=” and TABLE_NAME=”;


MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command itís done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.


InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB