I have the same MySQL SQL statement running on 2 different databases (my local machine and my production machine). The one on my local machine runs faster while the one on production is slow. Here are the EXPLAIN results on each.
我有相同的MySQL SQL语句运行在两个不同的数据库(本地机器和生产机器)上。我本地机器上的那个运行得更快，而生产上的那个运行得慢。这里是每个人的解释结果。
Local Machine 
本地机器
Production Machine 
生产机器
I'd rather not post the exact query if I don't have to.
如果不需要，我宁愿不发布确切的查询。
The only difference I can tell between the 2 is that my local machine is running version 5.6 while the production server is running 5.5. Also, the data on my server is 3 days old which isn't many records. Specifically, I'm looking at row 2 of the explain where one type reads "ref" and the other reads "ALL" and there's a difference of over 28k rows being read. The only difference between the 2 structures is the TimeModified field which isn't being used by the query
我能区分这两个之间的唯一区别是，我的本地机器运行的是version 5.6，而生产服务器运行的是5.5。而且，我的服务器上的数据是3天前的，记录不多。具体地说，我在看第2行，其中一个类型读取“ref”，另一个读“ALL”，有超过28k行被读取。这两个结构之间的惟一区别是TimeModified字段，查询不使用该字段
2 个解决方案
#1
2
In MySQL 5.5 and earlier, a derived table never had indexes. The only way a derived table would be accessed was by a full scan. (That's the ALL you see in the EXPLAIN output from the 5.5 server.)
在MySQL 5.5和更早的版本中，派生表从来没有索引。要访问派生表的惟一方法是进行完整的扫描。(这就是您在5.5服务器的EXPLAIN输出中看到的全部内容。)
With MySQL 5.6.3, MySQL has the ability to add an index to a derived table, which can improve performance. (Note the name of the index: <auto_key1> in the EXPLAIN output from the 5.6 server).
使用MySQL 5.6.3, MySQL能够向派生表添加索引，从而提高性能。注意索引的名称:
Reference: https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
参考:https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
#2
0
I did put this as a comment first, but I think it also is the answer :-)
我把这句话放在了评论的前面，但我认为这也是答案:-)
Your table structures might be identical, but your data probably isn't.
您的表结构可能是相同的，但是您的数据可能不是相同的。
The number of rows in your tables will influence the execution plan.
表中的行数将影响执行计划。
#1
2
In MySQL 5.5 and earlier, a derived table never had indexes. The only way a derived table would be accessed was by a full scan. (That's the ALL you see in the EXPLAIN output from the 5.5 server.)
在MySQL 5.5和更早的版本中，派生表从来没有索引。要访问派生表的惟一方法是进行完整的扫描。(这就是您在5.5服务器的EXPLAIN输出中看到的全部内容。)
With MySQL 5.6.3, MySQL has the ability to add an index to a derived table, which can improve performance. (Note the name of the index: <auto_key1> in the EXPLAIN output from the 5.6 server).
使用MySQL 5.6.3, MySQL能够向派生表添加索引，从而提高性能。注意索引的名称:
Reference: https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
参考:https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
#2
0
I did put this as a comment first, but I think it also is the answer :-)
我把这句话放在了评论的前面，但我认为这也是答案:-)
Your table structures might be identical, but your data probably isn't.
您的表结构可能是相同的，但是您的数据可能不是相同的。
The number of rows in your tables will influence the execution plan.
表中的行数将影响执行计划。