Re: Need help understanding optimizer behavior in 5.6

[Aaron Craven]

Øystein Grøvlen Wrote:
-------------------------------------------------------
> ...
> 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.)

I don't disagree. I just don't follow your meaning regarding "50% of real value". What does that mean?

> 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 :-) ).

I understand.

> 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.

Okay. The only reason I thought this distribution might be the cause is that it seemed to make the problem easier to reproduce. It is entirely possible that it is a red herring, though.

> In order to understand what really goes wrong
> here, it would be nice if you could provide
> optimizer_trace for this issue.

I'm glad you pointed this tool out to me. I was able (after some initial mistakes) to create this and I will post it below. Please note that the data I tested against has changed slightly, as described in the bug report I posted. I will also post this information there.

> 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)

This was my conclusion as well, but I very much dislike forcing an access plan unless I am certain there is a bug or I am working with a known edge case. Doing so takes control away from the optimizer and I generally think it's a lot smarter than I am.

Optimizer trace follows:
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select sql_no_cache `e`.`id` AS `id`,`g`.`id` AS `id`,`c`.`id` AS `id` from ((`entry` `e` join `entry_group` `g` on((`g`.`id` = `e`.`group_id`))) join `category` `c` on((`c`.`id` = `g`.`category_id`))) where (`e`.`uneven_val` between 300 and 8000)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
] /* transformations */,
"expanded_query": "/* select#1 */ select sql_no_cache `e`.`id` AS `id`,`g`.`id` AS `id`,`c`.`id` AS `id` from `entry` `e` join `entry_group` `g` join `category` `c` where ((`e`.`uneven_val` between 300 and 8000) and (`c`.`id` = `g`.`category_id`) and (`g`.`id` = `e`.`group_id`))"
} /* transformations_to_nested_joins */
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`e`.`uneven_val` between 300 and 8000) and (`c`.`id` = `g`.`category_id`) and (`g`.`id` = `e`.`group_id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`e`.`uneven_val` between 300 and 8000) and multiple equal(`c`.`id`, `g`.`category_id`) and multiple equal(`g`.`id`, `e`.`group_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`e`.`uneven_val` between 300 and 8000) and multiple equal(`c`.`id`, `g`.`category_id`) and multiple equal(`g`.`id`, `e`.`group_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`e`.`uneven_val` between 300 and 8000) and multiple equal(`c`.`id`, `g`.`category_id`) and multiple equal(`g`.`id`, `e`.`group_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`entry` `e`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`entry_group` `g`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`category` `c`",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`entry` `e`",
"field": "group_id",
"equals": "`g`.`id`",
"null_rejecting": false
},
{
"table": "`entry_group` `g`",
"field": "id",
"equals": "`e`.`group_id`",
"null_rejecting": false
},
{
"table": "`entry_group` `g`",
"field": "category_id",
"equals": "`c`.`id`",
"null_rejecting": false
},
{
"table": "`entry_group` `g`",
"field": "id",
"equals": "`e`.`group_id`",
"null_rejecting": false
},
{
"table": "`category` `c`",
"field": "id",
"equals": "`g`.`category_id`",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`entry` `e`",
"range_analysis": {
"table_scan": {
"rows": 249996,
"cost": 50546
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_jac_entry_on_group_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_jac_entry_on_uneven_val",
"usable": true,
"key_parts": [
"uneven_val",
"id"
] /* key_parts */
}
] /* potential_range_indices */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_jac_entry_on_uneven_val",
"ranges": [
"300 <= uneven_val <= 8000"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 23306,
"cost": 27968,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_jac_entry_on_uneven_val",
"rows": 23306,
"ranges": [
"300 <= uneven_val <= 8000"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 23306,
"cost_for_plan": 27968,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
},
{
"table": "`entry_group` `g`",
"table_scan": {
"rows": 24943,
"cost": 97
} /* table_scan */
},
{
"table": "`category` `c`",
"table_scan": {
"rows": 7,
"cost": 1
} /* table_scan */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`category` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 7,
"cost": 2.4,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2.4,
"rows_for_plan": 7,
"rest_of_plan": [
{
"plan_prefix": [
"`category` `c`"
] /* plan_prefix */,
"table": "`entry` `e`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_jac_entry_on_group_id",
"usable": false,
"chosen": false
},
{
"access_type": "range",
"rows": 23306,
"cost": 228406,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 228408,
"rows_for_plan": 163142,
"rest_of_plan": [
{
"plan_prefix": [
"`category` `c`",
"`entry` `e`"
] /* plan_prefix */,
"table": "`entry_group` `g`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23306,
"chosen": true
},
{
"access_type": "ref",
"index": "idx_jac_entry_group_on_category_id",
"rows": 1,
"cost": 163142,
"chosen": false
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 284343,
"rows_for_plan": 163142,
"chosen": true
}
] /* rest_of_plan */
},
{
"plan_prefix": [
"`category` `c`"
] /* plan_prefix */,
"table": "`entry_group` `g`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "idx_jac_entry_group_on_category_id",
"rows": 1781,
"cost": 372.32,
"chosen": true
},
{
"access_type": "scan",
"cost": 5085.6,
"rows": 24943,
"cause": "cost",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2511.9,
"rows_for_plan": 12467,
"rest_of_plan": [
{
"plan_prefix": [
"`category` `c`",
"`entry_group` `g`"
] /* plan_prefix */,
"table": "`entry` `e`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_jac_entry_on_group_id",
"rows": 4,
"cost": 49869,
"chosen": true
},
{
"access_type": "range",
"rows": 17480,
"cost": 4.07e8,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 62354,
"rows_for_plan": 49868,
"chosen": true
}
] /* rest_of_plan */
}
] /* rest_of_plan */
},
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`entry` `e`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_jac_entry_on_group_id",
"usable": false,
"chosen": false
},
{
"access_type": "range",
"rows": 23306,
"cost": 32629,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 32629,
"rows_for_plan": 23306,
"rest_of_plan": [
{
"plan_prefix": [
"`entry` `e`"
] /* plan_prefix */,
"table": "`category` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 7,
"cost": 32630,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 65260,
"rows_for_plan": 163142,
"pruned_by_cost": true
},
{
"plan_prefix": [
"`entry` `e`"
] /* plan_prefix */,
"table": "`entry_group` `g`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23306,
"chosen": true
},
{
"access_type": "ref",
"index": "idx_jac_entry_group_on_category_id",
"rows": 1,
"cost": 23306,
"chosen": false
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 60597,
"rows_for_plan": 23306,
"rest_of_plan": [
{
"plan_prefix": [
"`entry` `e`",
"`entry_group` `g`"
] /* plan_prefix */,
"table": "`category` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23306,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"cost_for_plan": 88564,
"rows_for_plan": 23306,
"pruned_by_cost": true
},
{
"plan_prefix": [
"`entry` `e`",
"`entry_group` `g`"
] /* plan_prefix */,
"table": "`category` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23306,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 88564,
"rows_for_plan": 23306,
"pruned_by_cost": true
}
] /* rest_of_plan */
}
] /* rest_of_plan */
},
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`entry_group` `g`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "idx_jac_entry_group_on_category_id",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 24943,
"cost": 5085.6,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 5085.6,
"rows_for_plan": 24943,
"rest_of_plan": [
{
"plan_prefix": [
"`entry_group` `g`"
] /* plan_prefix */,
"table": "`category` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 24943,
"chosen": true
},
{
"access_type": "scan",
"cause": "covering_index_better_than_full_scan",
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 35017,
"rows_for_plan": 24943,
"rest_of_plan": [
{
"plan_prefix": [
"`entry_group` `g`",
"`category` `c`"
] /* plan_prefix */,
"table": "`entry` `e`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_jac_entry_on_group_id",
"rows": 4,
"cost": 99773,
"chosen": true
},
{
"access_type": "range",
"rows": 17480,
"cost": 8.14e8,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": false
},
{
"plan_prefix": [
"`entry_group` `g`",
"`category` `c`"
] /* plan_prefix */,
"table": "`entry` `e`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_jac_entry_on_group_id",
"rows": 4,
"cost": 99773,
"chosen": true
},
{
"access_type": "range",
"rows": 17480,
"cost": 8.14e8,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 154744,
"rows_for_plan": 99772,
"pruned_by_cost": true
}
] /* rest_of_plan */
},
{
"plan_prefix": [
"`entry_group` `g`"
] /* plan_prefix */,
"table": "`entry` `e`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_jac_entry_on_group_id",
"rows": 4,
"cost": 99773,
"chosen": true
},
{
"access_type": "range",
"rows": 17480,
"cost": 8.14e8,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 124812,
"rows_for_plan": 99772,
"pruned_by_cost": true
}
] /* rest_of_plan */
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`e`.`group_id` = `g`.`id`) and (`g`.`category_id` = `c`.`id`) and (`e`.`uneven_val` between 300 and 8000))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`category` `c`",
"attached": null
},
{
"table": "`entry_group` `g`",
"attached": null
},
{
"table": "`entry` `e`",
"attached": "(`e`.`uneven_val` between 300 and 8000)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`category` `c`",
"access_type": "index_scan"
},
{
"table": "`entry_group` `g`"
},
{
"table": "`entry` `e`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}