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?