Mysql: How to disable index in innodb

I'm trying to speed up bulk insert in an InnoDB table by temporary disabling its indexes:

ALTER TABLE mytable DISABLE KEYS;

But it gives a warning:

+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytable' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

How can we disable the indexes?

What alternatives are there to avoid using the index when doing bulk inserts?

How can we speed up the process?

 Answers

Have you tried the following?

    SET autocommit=0; 
    SET unique_checks=0; 
    SET foreign_key_checks=0;

From the MySQL References https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

See Section "Bulk Data Loading Tips"

---

to reduce the costs for re-calculating the indexes you should insert the data either using DATA INFILE or using Mysql Multi Row Inserts, like

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

-> so inserting several rows with one statement.

How many rows one can insert with one statement depends on the max_allowed_packet mysql setting.

How can I check MySQL engine type for a specific table?

My MySQL database contains several tables using different storage engines (specifically myisam and innodb). How can I find out which tables are using which engine?

 Answers

SHOW TABLE STATUS WHERE Name = 'xxx'

This will give you (among other things) an Engine column, which is what you want.

---

SHOW CREATE TABLE <tablename>;

Less parseable but more readable than SHOW TABLE STATUS.

---

Bit of a tweak to Jocker's response (I would post as a comment, but I don't have enough karma yet):

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database' AND ENGINE IS NOT NULL;

This excludes MySQL views from the list, which don't have an engine.

---

mysqlshow -i <database_name>

will show the info for all tables of a specific database.

mysqlshow -i <database_name> <table_name> 

will do so just for a specific table.

---

If you are a linux user:

To show the engines for all tables for all databases on a mysql server, without tables information_schema, mysql, performance_schema:

less < <({ for i in $(mysql -e "show databases;" | cat | grep -v -e Database-e information_schema -e mysql -e performance_schema); do echo "--------------------$i--------------------";  mysql -e "use $i; show table status;"; done } | column -t)

You might love this, if you are on linux, at least.

Will open all info for all tables in less, press -S to chop overly long lines.

Example output:

--------------------information_schema--------------------
Name                                                        Engine              Version  Row_format  Rows   Avg_row_length  Data_length  Max_data_length     Index_length  Data_free  Auto_increment  Create_time  Update_time  Check_time  C
CHARACTER_SETS                                              MEMORY              10       Fixed       NULL   384             0            16434816            0             0          NULL            2015-07-13   15:48:45     NULL        N
COLLATIONS                                                  MEMORY              10       Fixed       NULL   231             0            16704765            0             0          NULL            2015-07-13   15:48:45     NULL        N
COLLATION_CHARACTER_SET_APPLICABILITY                       MEMORY              10       Fixed       NULL   195             0            16357770            0             0          NULL            2015-07-13   15:48:45     NULL        N
COLUMNS                                                     MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15:48:45     2015-07-13  1
COLUMN_PRIVILEGES                                           MEMORY              10       Fixed       NULL   2565            0            16757145            0             0          NULL            2015-07-13   15:48:45     NULL        N
ENGINES                                                     MEMORY              10       Fixed       NULL   490             0            16574250            0             0          NULL            2015-07-13   15:48:45     NULL        N
EVENTS                                                      MyISAM              10       Dynamic     NULL   0               0            281474976710655     1024          0          NULL            2015-07-13   15:48:45     2015-07-13  1
FILES                                                       MEMORY              10       Fixed       NULL   2677            0            16758020            0             0          NULL            2015-07-13   15:48:45     NULL        N
GLOBAL_STATUS                                               MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15:48:45     NULL        N
GLOBAL_VARIABLES                                            MEMORY              10       Fixed       NULL   3268            0            16755036            0             0          NULL            2015-07-13   15:48:45     NULL        N
KEY_COLUMN_USAGE                                            MEMORY              10       Fixed       NULL   4637            0            16762755            0 

.
.
.

---

Yet another way, perhaps the shortest to get status of a single or matched set of tables:

SHOW TABLE STATUS LIKE 'table';

You can then use LIKE operators for example:

SHOW TABLE STATUS LIKE 'field_data_%';