I have a database "algebra" with a table "questions" with 1,033,990 rows. The records have an attribute 'solved' that is INT. I have a simple query
select count(*) from questions where solved = 0
I have two servers with similar CPUs. On both servers the tables are the same. (database is a replica of production). They are on SSDs. One server has Ubuntu 14.04 with MySQL 5.5.49, and another server has Ubuntu 16.04 with MySQL 5.7.12.
The problem is that this query takes only 0.009s on MySQL 5.5, but takes 0.304s on MySQL 5.7. Which is 34 times SLOWER!!!
The query plans are roughly similar:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE questions NULL index NULL by_topic_solved 97 NULL 1033990 10.00 Using where; Using index
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE questions index NULL by_topic_solved 97 NULL 1033989 Using where; Using index
Why this could be the case baffles me. I have seen other, more complicated performance problems with this new 5.7 setup, but this is the simplest problem to be tackling in terms of the underlying query.
I am grasping to find the explanation for this or where to start. my.cnf was roughly similar between the two. Any suggestions would be gratefully accepted.