MySQL Forums
Forum List  »  Optimizer & Parser

Re: Explain estimate completely wrong for range when partitioned by day
Posted by: Blake Harps
Date: February 14, 2014 03:07PM

Trace:

Quote

root@local-3306 [(none)]>SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: SELECT count(*) as cnt FROM debuglog_test.stats_201402 _BASE WHERE _BASE.`created` BETWEEN '2014-02-14 00:00:00' AND '2014-02-14 23:59:59'
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select count(0) AS `cnt` from `debuglog_test`.`stats_201402` `_BASE` where (`debuglog_test`.`_BASE`.`created` between '2014-02-14 00:00:00' and '2014-02-14 23:59:59')"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`debuglog_test`.`_BASE`.`created` between '2014-02-14 00:00:00' and '2014-02-14 23:59:59')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`debuglog_test`.`_BASE`.`created` between '2014-02-14 00:00:00' and '2014-02-14 23:59:59')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`debuglog_test`.`_BASE`.`created` between '2014-02-14 00:00:00' and '2014-02-14 23:59:59')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`debuglog_test`.`_BASE`.`created` between '2014-02-14 00:00:00' and '2014-02-14 23:59:59')"
}
]
}
},
{
"table_dependencies": [
{
"table": "`debuglog_test`.`stats_201402` `_BASE`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "`debuglog_test`.`stats_201402` `_BASE`",
"range_analysis": {
"table_scan": {
"rows": 1402260,
"cost": 444363
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "created",
"usable": true,
"key_parts": [
"created"
]
},
{
"index": "idx_status",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_site_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_campaign_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_campaign_ad_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_campaign_url_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_publisher_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_site_event_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_country_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_region_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_publisher_sub_publisher_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_publisher_sub_site_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_publisher_sub_campaign_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_publisher_sub_adgroup_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_publisher_sub_ad_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_adv_pub_fb",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_test_profile_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_adv_fb",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_adv_pub_site",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_adv_status_pub_fb",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_adv_pub_status",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_device_id",
"usable": false,
"cause": "not_applicable"
}
],
"best_covering_index_scan": {
"index": "idx_status",
"cost": 283362,
"chosen": true
},
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "created",
"ranges": [
"2014-02-14 00:00:00 <= created <= 2014-02-14 23:59:59"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 1,
"cost": 2.21,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "created",
"rows": 1,
"ranges": [
"2014-02-14 00:00:00 <= created <= 2014-02-14 23:59:59"
]
},
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`debuglog_test`.`stats_201402` `_BASE`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"rows": 1,
"cost": 2.41,
"chosen": true
}
]
},
"cost_for_plan": 2.41,
"rows_for_plan": 1,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`debuglog_test`.`_BASE`.`created` between '2014-02-14 00:00:00' and '2014-02-14 23:59:59')",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`debuglog_test`.`stats_201402` `_BASE`",
"attached": "(`debuglog_test`.`_BASE`.`created` between '2014-02-14 00:00:00' and '2014-02-14 23:59:59')"
}
]
}
},
{
"refine_plan": [
{
"table": "`debuglog_test`.`stats_201402` `_BASE`",
"access_type": "range"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.07 sec)

root@local-3306 [(none)]>

Count to show that data actually exists in that range:

Quote

root@local-3306 [(none)]>SELECT count(*) as cnt FROM debuglog_test.stats_201402 _BASE WHERE _BASE.`created` BETWEEN '2014-02-14 00:00:00' AND '2014-02-14 23:59:59';
+--------+
| cnt |
+--------+
| 763360 |
+--------+
1 row in set (0.55 sec)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.