Hi Martin,
It seems to me that there must be some differences in the statistics used by the query optimizer on your two systems. The output of SHOW INDEX should show the statistics that the optimization is based on.
One reason for differences in statistics may be that one or both systems is using the traditional transient statistics that will be recomputed on each start-up. MySQL 5.6 introduced persistent statistics which will be more stable.
Is any of the databases upgraded from 5.5 or earlier, and if so, did you run the upgrade script?
To check if persistent statistics is in use, check if the tables mysql.innodb_table_stats and mysql.innodb_index_stats contain information about this table.
Another issue with the given query in MySQL 5.6 is the effect of the new variable eq_range_index_dive_limit. This variable affects how MySQL estimates the number of matches for a large IN-expression. That is, if the number of values in the IN-list is greater than eq_range_index_dive_limit, in order to reduce the cost of optimization, the optimizer will not actually check the B-tree for an estimate for every given value, but base it plan on the precomputed statistics. However, this may give bad plans if the general statistics is not representative for the values to be looked up (Ref. Bug#70586). You can set eq_range_index_dive_limit to 0 to make sure it collects statistics the same way as in earlier versions.
With respect to your particular query, you have what we call an outer reference in your sub-query (ADDRESS.ForeignEditPermission = 65535). This limits a bit the kind of sub-query optimization that we can do. (E.g., we cannot use materialization where the result of the sub-query is stored in a temporary table.)
Is the above condition strictly necessary, or could it be pulled out to the main query?
We are very interested in understanding more about what happens with your query, and I hope you may be willing to help us. One option is that you provide us with the optimizer trace (See
http://dev.mysql.com/doc/internals/en/optimizer-tracing.html for how to do this). That would tell us why the optimizer ends up with a specific query plan. Another option is that you provide us with a sample database that we can use to reconstruct this ourselves. If the latter, I suggest that you create a bug report at bugs.mysql.com and upload your data to this bug report.
Thanks,
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway