Different execution with same query

Question

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

• Purefan: both databases have the same number of rows and 5.7 was configured with Percona's configuration wizard
• Randolph West: Are the statistics up to date? Try using the ANALYZE command.
• Purefan: @RandolphWest: I have just checked and yes, all tables on both servers are ok
• Nawaz Sohail: the major difference is that in mysql 5.5 it has opted for a greedy index(index seek same for order by) and in mysql57 it has not gone for any index as shared explain plan does not show any index shown under Key column. You can try dropping the existing index and re-creating them or doing optimize table to update statistics. hope it helps
• ypercubeᵀᴹ: I've edited your question to make it a bit more clear.

Answer 1

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.

Answer 2

It would be worth trying to make the index on VisitId cover this query. If you change the index definition like this:

KEY VisitId (VisitId, PageId)

then there would be a better candidate index on the pageview table.

• a_vlad: (PageId, VisitId) - also will work, which better need test on real data
• Purefan: Thanks for the suggestion! Im now dropping the index VisitId and re-creating it to include PageId, will take a while so I'll post back when it finishes
• Purefan: Unfortunately it did not work, going to edit the question with the explain
• a_vlad: My SQL not always choose proper index if You have more than need - drop indexes paged and one of other - or ALL or VisitId (leave only one of them) 1 SIMPLE pageview index PageIdVisitId PageIdVisitId 10 10356 100.00 Using where; Using index; Using temporary; Using filesort 1 SIMPLE page eq_ref PRIMARY PRIMARY 4 test_db.pageview.PageId 1 100.00