Skip to content

Instantly share code, notes, and snippets.

@MrCsabaToth

MrCsabaToth/contactinfo_optimize_trace_composite2_unique.txt

Created April 18, 2016 19:08
Show Gist options
  • Save MrCsabaToth/62ed290b6c8ddd4f40a172a87a8c0f84 to your computer and use it in GitHub Desktop.
Save MrCsabaToth/62ed290b6c8ddd4f40a172a87a8c0f84 to your computer and use it in GitHub Desktop.
Download ZIP
Optimize trace output for a long runnign query
Raw
contactinfo_optimize_trace_composite2_unique.txt
'{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `main_contactinfo`.`id` AS `id`,`main_contactinfo`.`first_name` AS `first_name`,`main_contactinfo`.`last_name` AS `last_name`,`main_contactinfo`.`club_name` AS `club_name`,`main_contactinfo`.`email` AS `email`,`main_contactinfo`.`team_name_id` AS `team_name_id`,`main_contactinfo`.`address` AS `address`,`main_contactinfo`.`city` AS `city`,`main_contactinfo`.`state` AS `state`,`main_contactinfo`.`zip` AS `zip`,`main_contactinfo`.`contact_type` AS `contact_type`,`main_contactinfo`.`relation` AS `relation`,`main_contactinfo`.`player_id` AS `player_id`,`main_contactinfo`.`last_modified` AS `last_modified`,`main_contactinfo`.`object_id` AS `object_id`,`main_contactinfo`.`sync_inactive` AS `sync_inactive`,`main_contactinfo`.`customer_account_id` AS `customer_account_id`,`main_contactinfo`.`is_asgr` AS `is_asgr`,`main_contactinfo`.`note` AS `note`,`main_contactinfo`.`twitter` AS `twitter`,`main_contactinfo`.`facebook` AS `facebook`,`main_contactinfo`.`instagram` AS `instagram`,`main_contactinfo`.`other` AS `other`,`main_contactinfo`.`aau` AS `aau`,`main_contactinfo`.`aau_coachnum` AS `aau_coachnum`,`main_contactinfo`.`hs_coachnum` AS `hs_coachnum`,`main_contactinfo`.`parent_status` AS `parent_status` from `main_contactinfo` where ((`main_contactinfo`.`customer_account_id` = 10112) and (((`main_contactinfo`.`sync_inactive` = 1) and (`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'')) or ((`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'') and (`main_contactinfo`.`sync_inactive` = 0)) or ((`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`last_modified` = ''2015-09-28 17:40:21'') and (`main_contactinfo`.`id` > 19968645)))) order by `main_contactinfo`.`last_modified`,`main_contactinfo`.`id` limit 83"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`main_contactinfo`.`customer_account_id` = 10112) and (((`main_contactinfo`.`sync_inactive` = 1) and (`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'')) or ((`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'') and (`main_contactinfo`.`sync_inactive` = 0)) or ((`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`last_modified` = ''2015-09-28 17:40:21'') and (`main_contactinfo`.`id` > 19968645))))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((((`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'') and multiple equal(1, `main_contactinfo`.`sync_inactive`)) or ((`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'') and multiple equal(0, `main_contactinfo`.`sync_inactive`)) or ((`main_contactinfo`.`id` > 19968645) and multiple equal(0, `main_contactinfo`.`sync_inactive`) and multiple equal(''2015-09-28 17:40:21'', `main_contactinfo`.`last_modified`))) and multiple equal(10112, `main_contactinfo`.`customer_account_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((((`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'') and multiple equal(1, `main_contactinfo`.`sync_inactive`)) or ((`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'') and multiple equal(0, `main_contactinfo`.`sync_inactive`)) or ((`main_contactinfo`.`id` > 19968645) and multiple equal(0, `main_contactinfo`.`sync_inactive`) and multiple equal(''2015-09-28 17:40:21'', `main_contactinfo`.`last_modified`))) and multiple equal(10112, `main_contactinfo`.`customer_account_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((((`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'') and multiple equal(1, `main_contactinfo`.`sync_inactive`)) or ((`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'') and multiple equal(0, `main_contactinfo`.`sync_inactive`)) or ((`main_contactinfo`.`id` > 19968645) and multiple equal(0, `main_contactinfo`.`sync_inactive`) and multiple equal(''2015-09-28 17:40:21'', `main_contactinfo`.`last_modified`))) and multiple equal(10112, `main_contactinfo`.`customer_account_id`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`main_contactinfo`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`main_contactinfo`",
"field": "customer_account_id",
"equals": "10112",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`main_contactinfo`",
"range_analysis": {
"table_scan": {
"rows": 20543972,
"cost": 4.27e6
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "composite_index",
"usable": true,
"key_parts": [
"id",
"customer_account_id",
"last_modified",
"sync_inactive"
]
},
{
"index": "main_contactinfo_15d2e69b",
"usable": false,
"cause": "not_applicable"
},
{
"index": "main_contactinfo_team_name_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "main_contactinfo_f4b91458",
"usable": true,
"key_parts": [
"customer_account_id",
"id"
]
},
{
"index": "main_contactinfo_last_modified_4769d8a97da686d4_uniq",
"usable": true,
"key_parts": [
"last_modified",
"id"
]
},
{
"index": "main_contactinfo_object_id_6284ede570a32735_uniq",
"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": "composite_index",
"chosen": false,
"cause": "unknown"
},
{
"index": "main_contactinfo_f4b91458",
"ranges": [
"10112 <= customer_account_id <= 10112"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 42294,
"cost": 50754,
"chosen": true
},
{
"index": "main_contactinfo_last_modified_4769d8a97da686d4_uniq",
"ranges": [
"0x9997391a15 <= last_modified <= 0x9997391a15 AND 19968645 < id",
"0x9997391a15 < last_modified"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 10272032,
"cost": 1.23e7,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "main_contactinfo_f4b91458",
"rows": 42294,
"ranges": [
"10112 <= customer_account_id <= 10112"
]
},
"rows_for_plan": 42294,
"cost_for_plan": 50754,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`main_contactinfo`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "main_contactinfo_f4b91458",
"rows": 42294,
"cost": 50753,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "main_contactinfo_f4b91458"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 13.55,
"rows_for_plan": 5730.9,
"cost_for_plan": 50753,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`main_contactinfo`.`customer_account_id` = 10112) and (((`main_contactinfo`.`sync_inactive` = 1) and (`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'')) or ((`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'')) or ((`main_contactinfo`.`last_modified` = ''2015-09-28 17:40:21'') and (`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`id` > 19968645))))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`main_contactinfo`",
"attached": "(((`main_contactinfo`.`sync_inactive` = 1) and (`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'')) or ((`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'')) or ((`main_contactinfo`.`last_modified` = ''2015-09-28 17:40:21'') and (`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`id` > 19968645)))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`main_contactinfo`.`last_modified`,`main_contactinfo`.`id`",
"items": [
{
"item": "`main_contactinfo`.`last_modified`"
},
{
"item": "`main_contactinfo`.`id`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`main_contactinfo`.`last_modified`,`main_contactinfo`.`id`"
}
},
{
"added_back_ref_condition": "((`main_contactinfo`.`customer_account_id` <=> 10112) and (((`main_contactinfo`.`sync_inactive` = 1) and (`main_contactinfo`.`last_modified` >= ''2016-04-17 00:16:55'')) or ((`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`last_modified` > ''2015-09-28 17:40:21'')) or ((`main_contactinfo`.`last_modified` = ''2015-09-28 17:40:21'') and (`main_contactinfo`.`sync_inactive` = 0) and (`main_contactinfo`.`id` > 19968645))))"
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"rows_estimation": {
"table": "`main_contactinfo`",
"index": "main_contactinfo_last_modified_4769d8a97da686d4_uniq",
"range_analysis": {
"table_scan": {
"rows": 20543972,
"cost": 2.47e7
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "composite_index",
"usable": false,
"cause": "not_applicable"
},
{
"index": "main_contactinfo_15d2e69b",
"usable": false,
"cause": "not_applicable"
},
{
"index": "main_contactinfo_team_name_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "main_contactinfo_f4b91458",
"usable": false,
"cause": "not_applicable"
},
{
"index": "main_contactinfo_last_modified_4769d8a97da686d4_uniq",
"usable": true,
"key_parts": [
"last_modified",
"id"
]
},
{
"index": "main_contactinfo_object_id_6284ede570a32735_uniq'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment