Does mysqldump export indices, by default?

Question

I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option:

--disable-keys, -K

which suggests, that it actually does export the indices. But I don't want to trust my interpretation and I want to make sure I got it right (or wrong ;-)). Can anyone confirm that?

Answer 1

No, it does not export indexes. Indexes are rebuilt upon loading the mysqldump back into mysql. The options you found "--disable-keys" cause the the mysqldump to write something like this before the table's load via INSERTs:

DROP TABLE IF EXISTS `tblAccountLinks`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tblAccountLinks` (
  `ID` int(11) NOT NULL auto_increment,
  `FirmNo` varchar(10) NOT NULL,
  `CustomerNo` varchar(20) NOT NULL,
  `AccountNo` varchar(20) NOT NULL,
  `LinkType` smallint(6) NOT NULL,
  `AccessLevel` smallint(6) NOT NULL,
  `Status` smallint(6) NOT NULL,
  `CreatedOn` datetime NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=27023 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `tblAccountLinks`
--

LOCK TABLES `tblAccountLinks` WRITE;
/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;
INSERT INTO `tblAccountLinks` VALUES (1,'F0001','C001','T00000001',1,2,1,'2008-06-30 07:55:43'),(2,'
F0001','C001','T00000002',2,2,1,'2008-06-30 07:55:43'),(3,'F0001','C002','27601012',1,2,1,'2008-06-3 ...

The line after LOCK TABLES is

/*!40000 ALTER TABLE `tblAccountLinks` DISABLE KEYS */;

This is what the --disable-keys option embeds in the mysqldump.

Also, this is embedded after all the INSERTs are done

/*!40000 ALTER TABLE `tblAccountLinks` ENABLE KEYS */;
UNLOCK TABLES;

CAVEAT #1

DISABLE KEYS and ENABLE KEYS were implemented to disable the reloading of nonunique indexes while a table is being reloaded. Primary Keys and Unique Keys are not disabled. They are loaded at the same moment the INSERTs are being. Once you ENABLE KEYS, the nonunique indexes are rebuilt via sorting (or using the MyISAM key cache is there isn't enough available memory)

Unfortunately, DISABLE KEYS and ENABLE KEYS only work for MyISAM tables, not InnoDB.

CAVEAT #2

You do not have to --disable-keys. You could disable DISABLE KEYS (no pun intended) them with --skip-disable-keys:

  -K, --disable-keys  '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
                      '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
                      in the output.
                      (Defaults to on; use --skip-disable-keys to disable.)

This could result in a slower load and a potential lopsiding of the index pages for nonunique indexes.

CAVEAT #3

You can dump the actual InnoDB tablespaces (MySQL 5.5.12)

  -Y, --all-tablespaces 
                      Dump all the tablespaces.
  -y, --no-tablespaces 
                      Do not dump any tablespace information.