7

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:

Slow server:

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

Fast Server:

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.

5
  • 1
    Are you absolutely sure either neither server or both servers have warm caches? That is, what specific steps have you taken to ensure this? Jun 13, 2016 at 1:00
  • On the problematic slow server with mysql 5.7, I re-ran this query many times.The resulting timings were slightly different, like between 0.301 to 0.309 seconds, but not greatly so. I do not believe that it is a cache issue. Jun 13, 2016 at 1:08
  • 1
    I have fully understood this issue. What changed is that in the new mysql, query cache is disabled due to default setting of query_cache_type = OFF. When I re-enabled query cache by setting query_cache_type=1, better performance came right back. Since my site uses 100 times more SELECT queries than INSERT/UPDATE queries, using query cache makes sense for me. Thanks for pointing me to look in this direction! Jun 13, 2016 at 2:57
  • Igor, you may want to add that as an answer. Nice find! Be aware that the query cache (last time I checked) only works for EXACT matches, and only so long as the data does not change. Jun 13, 2016 at 12:38
  • I just did that, I did not realize I could provide an answer. Great website going here. And thanks for your help and encouragement Chris. Jun 13, 2016 at 16:42

1 Answer 1

8

I have fully understood this issue. What changed is that in the new mysql, query cache is disabled due to default setting of query_cache_type = OFF. When I re-enabled query cache by setting query_cache_type=1, better performance came right back. Since my site uses 100 times more SELECT queries than INSERT/UPDATE queries, using query cache makes sense for me. Thanks for pointing me to look in this direction!

2
  • Note that the query cache helps if you are running the EXACT same query (same exact string, even down to the case) AND the table is unchanged. In many cases, the query cache isn't actually all that useful. Still, very nice find and thank you for writing up your solution. This will be helpful to others! Jun 13, 2016 at 20:10
  • Some years later ... and I have the same problem from MySQL 5 to 8. Query Cache is removed, and I still have more SELECT queries than insert / updates.Do I need to code my own caching layer, or is there something that I can use? :-/ Jan 13, 2021 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.