Hi Aaron,
It difficult to say anything about why the other plan is not chosen based on the EXPLAIN plan for the chosen plans. However, AFAICT, the content of the rows columns of the EXPLAIN is as expected. (Given that pre-5.7 versions of MySQL all show just 50% of real value for InnoDB tables.) However, I cannot exclude that 5.0 through less accurate statistics could come up with a more efficient plan. (Sometimes two wrongs do make a right :-) ).
Wrt the uneven distribution of uneven_val, the pre-collected statistics should not matter since the size of the range will be estimated based on the actual distance in the B-tree between the low-end and high-end value.
In order to understand what really goes wrong here, it would be nice if you could provide optimizer_trace for this issue. That would provide information on the reasoning behind the decisions made by the query optimizer. (See
https://dev.mysql.com/doc/internals/en/optimizer-tracing.html for a description on how to generate the optimizer trace.)
As you have noticed, using LEFT JOIN may change join order since left table needs to be processed before right table. However, I do not think your LEFT JOIN variant is equivalent to your original query. To be equivalent, I think the addition to the WHERE clause needs to be "c.id IS NOT NULL". However, then MySQL would detect that this is not a really a LEFT JOIN and eliminate it, and you would have achieved nothing. Hence, I think your best bet is to add STRAIGHT_JOIN (or to make the query ignore indexes that makes the current plan useful, e.g., idx_entry_group_on_category_id)
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway