mysql 5.7 is much slower than mysql 5.6 in medium sql

Question

We are upgrading to mysql 5.7 and just discover that it is much slower than its 5.6 counter part. While both have almost identical config, the 5.6 version execute most of the sqls in milliseconds, while the other takes around 1 sec or more for a middle complex sql like the one below for example.

-- 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;

The tables have around 100K and 1K rows respectively. I noticed two interesting behaviors that I would like to "fix" :

1. Removing ORDER BY bring the exec time from ~1.2 sec to 0.0015 sec !!
2. The sql is not cached by mysql 5.7

Note: we do have cache query :

SHOW STATUS LIKE '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 many issues reported on 5.7 but don't get why this strange behaviors on this sql (still plenty of other sqls that run much slower on 5.7).

Here is the EXPLAIN, suggested 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;

Conservationist

So.. if you want speed, why do you use MyISAM? – Blancmange

can you post an EXPLAIN? – Inae

Use "profile" to analyze the time-cost in a SQL; it can shows the time-cost in each phase; – Televisor

@Blancmange : I though MyISAM is for speed ? InnoDB for transaction ? No ? – Conservationist

@NevilleK : to be honest I am not a experience in DB stuff, thanks for the tips. I posted the Explain – Conservationist

MyISAM is for nothing since it's an old engine designed to work fast on older computers and hard drives, InnoDB and TokuDB are for transactions and speed since both engines are capable of utilizing server resources way, way better. – Blancmange

@Blancmange Thanks for the info. I will definitely spend a few days to work on that – Conservationist

Amount of data you have is low, so if you use innodb and increase magic variable innodb_buffer_pool_size to say 2GB, you'll notice that queries are incredibly fast. – Blancmange

Answer 1

The INNER JOIN user user_table_alias ON user_table_alias.id = u.id looks useless. It only joins against itself and that technique is not used in the rest of the query.

There is no index on emailVerify. Which is indicated by the first row of the EXPLAIN. ('using where' means no index is used)

This query does not scale well with the size of the table, because the full table must be looked at before delimiting what 'recent users' are. So probably some internal buffer used by myisam is overflowed now. That is what 'using temporary' means. Using filesort means the order by is so big it uses a tempfile, which is bad for performance.

Rhea

actually, I did change user_table_alias.emailVerify = 1 => u.emailVerify = 1 and it is quick again. Dunno why, but in mysql5.6, both are freaking fast – Conservationist

Answer 2

Ok, thanks to NevilleK on Explain.

I figured out how to just ONLY fix this SQL:

 user_table_alias.emailVerify = 1 

to

u.emailVerify = 1

I do not know why, but in MySQL5.6, both are executed in milliseconds.

I guess I will have to review all the SQL (from other devs) , thanks to MySQL backward improvement

Conservationist

Answer 3

The self-join looks redundant.

I think you can re-write 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 "emailVerify" is a column with just a handful of values (0 and 1), and should therefore not be indexed. I also assume that "joinStamp" is some kind of time stamp (though the data type is integer). If that is true, you could create an index to speed this up further.

create index id_joinstamp on user (id, joinstamp)

Inae

Your query look alot nicer but, our "joining-to-user-tables" here is kinda reusable piece of code. That means many other tables such as "product", "post", "transaction"...etc are also joined to "user-user_suspend" . The code is from other team but I would not complain much. I will try to apply your advice – Conservationist

Answer 4

As a workaround you can try to use the Keyword STRAIGHT_JOIN after your first select. This keyword force mysql to join from left to right (MYSQL - What does STRAIGHT_JOIN do in this code?).

Midden