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.
Not using a built in command, but...
If an UPDATE
really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases.
Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to create the statements that will need to be run the remote system. Here is a demonstration:
drop table t1;
drop table t2;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
create table t2 as (select * from t1);
update t2 set c2 = null where c1 in (3,6);
select * from t1;
select * from t2;
select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1
join t2 on t1.c1 = t2.c1
where t1.c2 <> t2.c2 OR t2.c2 is null;
If you cannot query the remote database, you can update every row like this:
drop table t1;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1;
Best Answer
You can create views using
LIMIT
and give those view names to the end users. If you're using a development tool such as DBVisualizer, you can set the number of records to limit.As always, query labels allow for easy tracking of any requests.