Mysql – Different execution with same query

innodbmyisammysql-5.5mysql-5.7performancequery-performance

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:

enter image description here

The explain from 5.7 InnoDB is this:
enter image description here

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

And the explain:
enter image description here

Best Answer

It is difficult to say why MySQL Optimizer is choosing a different join order for 5.7 without seeing the optimizer trace for the query. However, you can force the same join order as 5.5 by using STRAIGHT_JOIN instead of INNER JOIN. That will tell MySQL to process the tables in the order they are listed.

Related Question