MySQL – Troubleshooting MySqlDump’s DISABLE KEYS Ineffectiveness on Import

backupindexinnodbMySQLmysqldump

I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!).

Scenario
I try to import some big* database dump on my local dev machine in reasonable time. We have a lot of KEYs attached to the tables which have turned out to be a bottleneck but are still important for our live system.

My approach after asking question above was to delete KEY ... statements from dump, import and re-add keys.

However I often find myself editing a current dump to import it locally and I stumbled across these funny "comments" (The disable/enable keys-lines)

--
-- Dumping data for table `monster`
--

LOCK TABLES `monster` WRITE;
/*!40000 ALTER TABLE `monster` DISABLE KEYS */;
INSERT … INSERT … INSERT
/*!40000 ALTER TABLE `monster` ENABLE KEYS */;
UNLOCK TABLES;

But in fact these "comments" are conditional MySql-Statements

That was news for me but ok, given the output form mysql --version everything looks fine to me:
mysql Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3

What I assume
The table is locked (fine, it's just me on the dev mashine). Then the keys as defined in table schema are disabled, data is imported, keys are enabled.
So during "data insertion"-phase there should be no time wasted on keys but rather examined after all data is inserted.

I would be thinking this is the same behaviour as if I delete all the KEY 'foo' (foo)'-lines from the dump, import the dump and run a script with ADD KEY 'foo' ... afterwards.

What I observe
It is way faster to manually delete the keys, import and re-add keys then relying on conditional DISABLE KEYS statements created my mysqldump

Manual editing of dump + mysql import + adding keys = 15+8+8 ≈ 30min
Plain mysql import: given up, (I'm just getting paid for 8 hours/day >:) )

I can't help but thinking I'm missing something very fundamental here (or database is trolling me).

Best Answer

You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were designed for MyISAM. As it says in the MySQL Documentation for ALTER TABLE:

If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

No mention is ever made of InnoDB in context with ALTER TABLE ... DISABLE/ENABLE KEYS;

Even if you run ALTER TABLE ... DISABLE KEYS; against an InnoDB table, it generates a warning:

mysql> show create table mytimes\G
*************************** 1. row ***************************
       Table: mytimes
Create Table: CREATE TABLE `mytimes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `totalTime` int(11) NOT NULL,
  `totalTimeDesc` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table mytimes disable keys;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytimes' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

That's why there is no affect. Please recall that @jynus mentioned the same thing in his answer in bulletpoint 7.

Keep in mind also that MyISAM keeps data and indexes in two separate files (.MYD for data, .MYI for indexes), so it would be trivial to disable and enable indexes. InnoDB keeps the PRIMARY KEY and row data in the same InnoDB pages (via the Clustered Index). Secondary indexes will carry the PRIMARY KEY as an attachment to every secondary index leaf entry. Since the data and indexes are intertwined via the Clustered Index, no one has, as of yet, attempted to implement DISABLE KEYS and ENABLE KEYS in InnoDB.

Related Question