Re: query with bad performance after migration to mysql 5.6
Hi
here output from Optimizer_Trace
mysql> Set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT messaggio.id, messaggio.data, allegati.id
-> FROM (
-> select m.id from messaggio m
-> WHERE m.idarticolo='repnews_271330' AND m.idpadre=0
-> AND m.pubblica=1 ORDER BY m.data DESC LIMIT 50
-> ) as m_padre
-> join messaggio on (m_padre.id=messaggio.idpadre OR m_padre.id=messaggio.id)
-> left join allegati on messaggio.id = allegati.idpost
-> ORDER BY messaggio.data DESC, messaggio.id desc, allegati.id asc;
+-------+------------+------+
| id | data | id |
+-------+------------+------+
| 20352 | 1169210692 | NULL |
.........
+-------+------------+------+
27 rows in set (29.45 sec)
mysql> Select * From Information_Schema.Optimizer_Trace;
| SELECT messaggio.id, messaggio.data, allegati.id
FROM (
select m.id from messaggio m
WHERE m.idarticolo='repnews_271330' AND m.idpadre=0
AND m.pubblica=1 ORDER BY m.data DESC LIMIT 50
) as m_padre
join messaggio on (m_padre.id=messaggio.idpadre OR m_padre.id=messaggio.id)
left join allegati on messaggio.id = allegati.idpost
ORDER BY messaggio.data DESC, messaggio.id desc, allegati.id asc | {
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1)) order by `m`.`data` desc limit 50"
}
]
}
},
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `messaggio`.`id` AS `id`,`messaggio`.`data` AS `data`,`allegati`.`id` AS `id` from (((/* select#2 */ select `m`.`id` AS `id` from `messaggio` `m` where ((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1)) order by `m`.`data` desc limit 50) `m_padre` join `messaggio` on(((`m_padre`.`id` = `messaggio`.`idpadre`) or (`m_padre`.`id` = `messaggio`.`id`)))) left join `allegati` on((`messaggio`.`id` = `allegati`.`idpost`))) order by `messaggio`.`data` desc,`messaggio`.`id` desc,`allegati`.`id`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"join_optimization": {
"select#": 2,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`m`.`idarticolo` = 'repnews_271330') and (`m`.`idpadre` = 0) and (`m`.`pubblica` = 1))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal('repnews_271330', `m`.`idarticolo`) and multiple equal(0, `m`.`idpadre`) and multiple equal(1, `m`.`pubblica`))"
}
]
}
},
{
"table_dependencies": [
{
"table": "`messaggio` `m`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`messaggio` `m`",
"field": "idpadre",
"equals": "0",
"null_rejecting": false
},
{
"table": "`messaggio` `m`",
"field": "idpadre",
"equals": "0",
"null_rejecting": false
},
{
"table": "`messaggio` `m`",
"field": "pubblica",
"equals": "1",
"null_rejecting": false
},
{
"table": "`messaggio` `m`",
"field": "idarticolo",
"equals": "'repnews_271330'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`messaggio` `m`",
"range_analysis": {
"table_scan": {
"rows": 2445095,
"cost": 567805
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idevento_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idpadre",
"usable": true,
"key_parts": [
"idpadre",
"id"
]
},
{
"index": "ideventoidpadre_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "ideventoidpadrepubblica_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idpadrepubblica_idx",
"usable": true,
"key_parts": [
"idpadre",
"pubblica",
"id"
]
},
{
"index": "ididpadrepubblica",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idUtente_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "data_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "nome",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idbrand_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idarticolo_idx",
"usable": true,
"key_parts": [
"idarticolo",
"id"
]
},
{
"index": "commento_ft_idx",
"usable": false,
"cause": "not_applicable"
},
{
"index": "titolo_ft_idx",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idpadre",
"ranges": [
"0 <= idpadre <= 0"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1222547,
"cost": 1.47e6,
"chosen": false,
"cause": "cost"
},
{
"index": "idpadrepubblica_idx",
"ranges": [
"0 <= idpadre <= 0 AND 1 <= pubblica <= 1"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 1222547,
"cost": 1.47e6,
"chosen": false,
"cause": "cost"
},
{
"index": "idarticolo_idx",
"ranges": [
"repnews_271330 <= idarticolo <= repnews_271330"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 26,
"cost": 32.21,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"intersecting_indices": [
{
"index": "idpadrepubblica_idx",
"index_scan_cost": 1791,
"cumulated_index_scan_cost": 1791,
"disk_sweep_cost": 278008,
"cumulated_total_cost": 279799,
"usable": true,
"matching_rows_now": 1.22e6,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "idarticolo_idx",
"index_scan_cost": 1.4032,
"cumulated_index_scan_cost": 1792.4,
"disk_sweep_cost": 257,
"cumulated_total_cost": 2049.4,
"usable": true,
"matching_rows_now": 13,
"isect_covering_with_this_index": false,
"chosen": true
},
{
"index": "idpadre",
"cumulated_total_cost": 2049.4,
"usable": false,
"cause": "does_not_reduce_cost_of_intersect"
}
],
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no_clustered_pk_index"
},
"chosen": false,
"cause": "cost"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idarticolo_idx",
"rows": 26,
"ranges": [
"repnews_271330 <= idarticolo <= repnews_271330"
]
},
"rows_for_plan": 26,
"cost_for_plan": 32.21,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`messaggio` `m`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idpadre",
"rows": 1.22e6,
"cost": 480861,
"chosen": true
},
{
"access_type": "ref",
"index": "idpadrepubblica_idx",
"rows": 1.22e6,
"cost": 480861,
"chosen": false
},
{
"access_type": "ref",
"index": "idarticolo_idx",
"rows": 26,
"cost": 31.2,
"chosen": true
},
{
"access_type": "range",
"cause": "heuristic_index_cheaper",
"chosen": false
}
]
},
"cost_for_plan": 31.2,
"rows_for_plan": 26,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`messaggio` `m`",
"attached": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0) and (`m`.`idarticolo` = 'repnews_271330'))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`m`.`data` desc",
"items": [
{
"item": "`m`.`data`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`m`.`data` desc"
}
},
{
"refine_plan": [
{
"table": "`messaggio` `m`",
"pushed_index_condition": "(`m`.`idarticolo` = 'repnews_271330')",
"table_condition_attached": "((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0))"
}
]
},
{
"added_back_ref_condition": "((`m`.`idarticolo` <=> 'repnews_271330') and ((`m`.`pubblica` = 1) and (`m`.`idpadre` = 0)))"
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`messaggio` `m` | 15072 | 0 |
mysql> Set optimizer_trace="enabled=off";