Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons.
This is on Amazon RDS, so my upgrade route is limited to dumping and recreating the database.
I will cheerfully admit I'm not a sophisticated DBA.
Problem 1: Wow is that slow!
It takes about 15 minutes to mysqldump our 160-million-odd rows. (Show table etc is coming, hold your horses.)
It took something like 50 hours to load it into a mysql 5.6 instance with engine craftily sed-script-ed to InnoDB.
Problem 2: Where are my rows?
select count(*) from node; on the current DB gives about 162 million. On the 5.6, it gives about 93 million. The load appeared to be successful, although I can't prove it; at least, there was no error message after the load terminated.
If it wasn't successful, that was really slow.
Problem 3: WOW is that slow!
So, select count(*) from node; completes in about no time at all -- between 0.00 and 0.03 seconds by the query results -- on 5.1. On 5.6 with InnoDB, it takes over a minute. The explain makes it obvious this is because of a difference in the way the query is optimized -- but unclear why it's different.
Tables and explains
MySQL 5.1
mysql> show create table node\G
*************************** 1. row ***************************
Table: node
Create Table: CREATE TABLE `node` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`graph` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`subject` varchar(200) NOT NULL,
`predicate` varchar(200) NOT NULL,
`object` mediumtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `nodeindex` (`graph`(20),`subject`(100),`predicate`(100),`object`(100)),
KEY `ix_node_subject` (`subject`),
KEY `ix_node_graph` (`graph`),
KEY `ix_node_object` (`object`(255)),
KEY `ix_node_predicate` (`predicate`),
KEY `node_po` (`predicate`,`object`(130)),
KEY `node_so` (`subject`,`object`(130)),
KEY `node_sp` (`subject`,`predicate`(130)),
FULLTEXT KEY `node_search` (`object`)
) ENGINE=MyISAM AUTO_INCREMENT=550671861 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select count(id) from node;
+-----------+
| count(id) |
+-----------+
| 163426434 |
+-----------+
1 row in set (0.00 sec)
mysql> explain select count(id) from node;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
MySQL 5.6
mysql> show create table node\G
*************************** 1. row ***************************
Table: node
Create Table: CREATE TABLE `node` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`graph` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`subject` varchar(200) NOT NULL,
`predicate` varchar(200) NOT NULL,
`object` mediumtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `nodeindex` (`graph`(20),`subject`(100),`predicate`(100),`object`(100)),
KEY `ix_node_subject` (`subject`),
KEY `ix_node_graph` (`graph`),
KEY `ix_node_object` (`object`(255)),
KEY `ix_node_predicate` (`predicate`),
KEY `node_po` (`predicate`,`object`(130)),
KEY `node_so` (`subject`,`object`(130)),
KEY `node_sp` (`subject`,`predicate`(130)),
FULLTEXT KEY `node_search` (`object`)
) ENGINE=InnoDB AUTO_INCREMENT=481239575 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
```
mysql> explain select count(id) from node;
+----+-------------+-------+-------+---------------+---------------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+----------+-------------+
| 1 | SIMPLE | node | index | NULL | ix_node_graph | 103 | NULL | 79671827 | Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+----------+-------------+
1 row in set (0.00 sec)
