How to disable index in innodb

Question

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?

Answer 1

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/8.0/en/optimizing-innodb-bulk-data-loading.html

See Section "Bulk Data Loading Tips"

Answer 2

There is a very good reason why you cannot execute DISABLE KEYS on an InnoDB table; InnoDB is not designed to use it, and MyISAM is.

In fact, here is what happens when you reload a mysqldump:

You will see a CREATE TABLE for a MyISAM table following by a write lock.

Before all the bulk inserts are run, a call to ALTER TABLE ... DISABLE KEYS is done.

What this does is turn off secondary indexes in the MyISAM table.

Then, bulk inserts are done. While this is being done, the PRIMARY KEY and all UNIQUE KEYS in the MyISAM table are being rebuilt. Before the UNLOCK TABLEs, a call ALTER TABLE ... ENABLE KEYS is done in order to rebuild all non-unique indexes linearly.

IMHO this operation was not coded into the InnoDB Storage Engine because all keys in a non-unique index come with the primary key entry from gen_clust_index (aka Clustered Index). That would be a very expensive operation since building a non-unique index would require O(n log n) running time to retrieve each unique key to attach to a non-unique key.

In light of this, posting a warning about trying to DISABLE KEYS/ENABLE KEYS on an InnoDB table is far easier than coding exceptions to the mysqldump for any special cases involving non-MyISAM storage engines.

Answer 3

A little late but... whatever... forget all the answers here, don't disable the indexes, there's no way, just drop them ALTER TABLE tablename DROP INDEX whatever, bulk insert the data, then ALTER TABLE tablename ADD INDEX whatever (whatever); the amount of time recreating the indexes is 1% of the bulk insert with indexes on it, like 400000 rows took 10 minutes with indexes and like 2 seconds without them..., cheers...

Answer 4

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.