Mysql 5.7 is much slower than mysql 5.6 in sql environment

Question

We upgrade to mysql 5.7 and simply find that it is much slower than its 5.6 counter. While both have an almost identical configuration, version 5.6 executes most sqls in milliseconds, and the other takes about 1 second or more for the average sql complex, for example, as shown below.

-- Getting most recent users that are email-verified and not banned SELECT `u`.* FROM `user` AS `u` INNER JOIN `user` user_table_alias ON user_table_alias.`id` = `u`.`id` LEFT JOIN `user_suspend` user_suspend_table_alias ON user_suspend_table_alias.`userId` = `user_table_alias`.`id` WHERE ( `user_suspend_table_alias`.`id` IS NULL ) AND `user_table_alias`.`emailVerify` = 1 ORDER BY `u`.`joinStamp` DESC LIMIT 1, 18 

Both tables are pretty simple and well indexed:

 -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(128) NOT NULL DEFAULT '', `username` varchar(32) NOT NULL DEFAULT '', `password` varchar(64) NOT NULL DEFAULT '', `joinStamp` int(11) NOT NULL DEFAULT '0', `activityStamp` int(11) NOT NULL DEFAULT '0', `accountType` varchar(32) NOT NULL DEFAULT '', `emailVerify` tinyint(2) NOT NULL DEFAULT '0', `joinIp` int(11) unsigned NOT NULL, `locationId` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), UNIQUE KEY `username` (`username`), KEY `accountType` (`accountType`), KEY `joinStamp` (`joinStamp`), KEY `activityStamp` (`activityStamp`) ) ENGINE=MyISAM AUTO_INCREMENT=89747 DEFAULT CHARSET=utf8 COMMENT='utf8_general_ci'; -- ---------------------------- -- Table structure for user_suspend -- ---------------------------- DROP TABLE IF EXISTS `user_suspend`; CREATE TABLE `user_suspend` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userId` int(11) DEFAULT NULL, `timestamp` int(11) DEFAULT NULL, `message` text NOT NULL, `expire` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `userId` (`userId`) ) ENGINE=MyISAM AUTO_INCREMENT=513 DEFAULT CHARSET=utf8; 

Tables have about 100K and 1K rows, respectively. I noticed two interesting behaviors that I would like to “fix”:

• Deleting ORDER BY causes the exec time to be between ~ 1.2 sec and 0.0015 sec.
• sql is not cached by mysql 5.7

Note. We have a cache request:

SHOW STATUS AS 'Qcache%'

 Qcache_free_blocks 19408 Qcache_free_memory 61782816 Qcache_hits 31437169 Qcache_inserts 2406719 Qcache_lowmem_prunes 133483 Qcache_not_cached 43555 Qcache_queries_in_cache 41691 Qcache_total_blocks 103951 

I googled and found out a lot of questions that were reported in 5.7, but don’t understand why this is strange behavior on this sql (there are still many other sql servers that work much slower on 5.7).

Here is the EXPLAIN proposed by Neville K.:

 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE user_table_alias NULL ALL PRIMARY NULL NULL NULL 104801 10.00 Using where; Usingtemporary; Usingfilesort 1 SIMPLE u NULL eq_ref PRIMARY PRIMARY 4 knn.base_user_table_alias.id 1 100.00 NULL 1 SIMPLE user_suspend_table_alias NULL ref userId userId 5 knn.base_user_table_alias.id 1 10.00 Using where; 

Answer 1

INNER JOIN user user_table_alias ON user_table_alias. id = u . id INNER JOIN user user_table_alias ON user_table_alias. id = u . id looks useless. It only connects to itself and this method is not used in the rest of the request.

There is no index on emailVerify . This is indicated on the first line of EXPLAIN. ('using where' means the index is not used)

This query does not scale very well with the size of the table, because before limiting what “recent users” are, you need to view the full table. So, probably, the internal buffer used by myisam is now being used. This is what “using temporary” means. Using filesort means that the order is so great that it uses a temporary file, which is bad for performance.

Answer 2

Self-connection seems redundant.

I think you can rewrite the query as follows:

 SELECT `u`.* FROM `user` AS `u` LEFT JOIN `user_suspend` user_suspend_table_alias ON user_suspend_table_alias.`userId` = `u`.`id` WHERE `user_suspend_table_alias`.`id` IS NULL AND `u`.`emailVerify` = 1 ORDER BY `u`.`joinStamp` DESC LIMIT 1, 18 

I assume that "emailVerify" is a column with multiple values ​​(0 and 1) and therefore should not be indexed. I also assume that "joinStamp" is a kind of timestamp (although the data type is integer). If so, you can create an index to speed it up.

 create index id_joinstamp on user (id, joinstamp) 

Answer 3

Ok thanks to NevilleK for an explanation.

I figured out how easy it is to ONLY fix this sql:

  user_table_alias.emailVerify = 1 

to

 u.emailVerify = 1 

I don’t know why, but in mysql5.6 both are executed in milliseconds.

I think I will have to view all sqls (from other developers), thanks to the improved Mysql improvement