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.
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
解决方案
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.)
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: in the EXPLAIN output from the 5.6 server).
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
