In a migration plan I am taking a mysql 5.5 database with only MyISAM tables and converted it into mysql 5.7 with InnoDB tables.
The create table statements are here: http://sqlfiddle.com/#!9/bb815d.
They are exactly the same except for the engine:
CREATE TABLE `pageview` (
`Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`VisitId` int(11) DEFAULT '0',
`PageId` int(11) DEFAULT '0',
`ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`),
KEY `VisitId` (`VisitId`),
KEY `PageId` (`PageId`),
KEY `All` (`PageId`,`ViewDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3245799556 DEFAULT CHARSET=utf8;
CREATE TABLE `page` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`SiteId` int(11) DEFAULT '0',
`Url` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`Name` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`Rating` tinyint(1) NOT NULL DEFAULT '0',
`RawUrl` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `Url` (`Url`,`SiteId`),
KEY `SiteId` (`SiteId`)
) ENGINE=InnoDB AUTO_INCREMENT=105689724 DEFAULT CHARSET=utf8;
When running this query the results are very different and performance is much worse in 5.7 with InnoDB that in 5.5 with MyISAM:
SELECT VisitId,SUM(page.Rating) AS Rating
FROM pageview
INNER JOIN page ON page.Id = pageview.PageId
GROUP BY VisitId
ORDER BY VisitId DESC
LIMIT 500000;
The explain from 5.5 MyISAM is this:
The explain from 5.7 InnoDB is this:
I don't know what can I do to improve performance in 5.7, or why has performance degraded so much.
The first thing that I notice is that the order of the tables is backwards, but also for the page
table its not using the PRIMARY
index as it was in 5.5 and I think this is the key difference.
Can you please suggest ways to improve this situation?
Update
After following the suggestion by mendosi I still do not get any improvement, here is the updated SHOW CREATE TABLE
:
CREATE TABLE `pageview` (
`Id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`VisitId` int(11) DEFAULT '0',
`PageId` int(11) DEFAULT '0',
`ViewDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`),
KEY `PageId` (`PageId`),
KEY `All` (`PageId`,`ViewDate`) USING BTREE,
KEY `VisitId` (`VisitId`,`PageId`)
) ENGINE=InnoDB AUTO_INCREMENT=3268418928 DEFAULT CHARSET=utf8
ANALYZE
command.