The following query:

EXPLAIN SELECT usr.*,act.*,cht.*
FROM users AS usr
LEFT JOIN active_u AS act USING (uid)
LEFT JOIN chat AS cht USING(uid)
ORDER BY usr.updated DESC LIMIT 0,10

shows this on MySQL 5.5.58 (Unix):


and this on MySQL 5.7.17 (Windows):


How comes that Mysql 5.5 optimizes the query perfectly, while 5.7 makes its best to slow down the query as much as possible? It simply scans all the rows , uses temporary tables, etc. Is there some mysql variable that specifies that queries must not be optimized, or something?

I can assure that the 5.5 Unix installation is using all default values.

I know I could refactor the query as shown here to optimize it for 5.7, but how comes the query was already optimized perfectly on Mysql 5.5?

Also: Why, and this happens often, the possible_keys field shows NULL but it actually ends up using a key, like the updated key in the 5.5 screenshot?

  • 1
    Are the table definitions identical? Are all indexes identical between the two databases? – ypercubeᵀᴹ Mar 5 '18 at 23:29
  • Do both versions have INDEX(updated)? Please provide EXPLAIN FORMAT=JSON SELECT ... on 5.7. – Rick James Mar 8 '18 at 20:22
  • Sorry for the delay, yes all indexes are identical and tables are practically the same, only the number of rows changes between versions. – andreszs Mar 17 '18 at 12:25
  • @andreszs - I think the JSON will have the clue; can you provide it, at least for 5.7? – Rick James Jun 10 '18 at 17:24

Your Answer

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

Browse other questions tagged or ask your own question.