使用duplicateweedout
2024-09-24 23:54:02
mysql> explain extended select * from cities where id in (select phonecode from countries group by name);
+----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+----------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+----------+-----------------+
| 1 | SIMPLE | countries | ALL | NULL | NULL | NULL | NULL | 246 | 100.00 | Start temporary |
| 1 | SIMPLE | cities | eq_ref | PRIMARY | PRIMARY | 4 | test.countries.phonecode | 1 | 100.00 | End temporary |
+----+-------------+-----------+--------+---------------+---------+---------+--------------------------+------+----------+-----------------+
2 rows in set, 1 warning (5.65 sec)
QUERY: explain extended select * from cities where id in (select phonecode from countries group by name)
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"transformations_to_subquery": [
"removed_grouping"
]
},
{
"expanded_query": "/* select#2 */ select `countries`.`phonecode` from `countries`"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"expanded_query": "/* select#1 */ select `cities`.`id` AS `id`,`cities`.`name` AS `name`,`cities`.`state_id` AS `state_id` from `cities` where `cities`.`id` in (/* select#2 */ select `countries`.`phonecode` from `countries`)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformations_to_nested_joins": {
"transformations": [
"semijoin"
],
"expanded_query": "/* select#1 */ select `cities`.`id` AS `id`,`cities`.`name` AS `name`,`cities`.`state_id` AS `state_id` from `cities` semi join (`countries`) where (1 and (`cities`.`id` = `countries`.`phonecode`))"
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(1 and (`cities`.`id` = `countries`.`phonecode`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and multiple equal(`cities`.`id`, `countries`.`phonecode`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and multiple equal(`cities`.`id`, `countries`.`phonecode`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(`cities`.`id`, `countries`.`phonecode`)"
}
]
}
},
{
"table_dependencies": [
{
"table": "`cities`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`countries`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`cities`",
"field": "id",
"equals": "`countries`.`phonecode`",
"null_rejecting": false
}
]
},
{
"pulled_out_semijoin_tables": [
]
},
{
"rows_estimation": [
{
"table": "`cities`",
"table_scan": {
"rows": 47478,
"cost": 161
}
},
{
"table": "`countries`",
"table_scan": {
"rows": 246,
"cost": 1
}
}
]
},
{
"execution_plan_for_potential_materialization": {
"steps": [
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`countries`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 246,
"cost": 50.2,
"chosen": true
}
]
},
"cost_for_plan": 50.2,
"rows_for_plan": 246,
"chosen": true
}
]
}
]
}
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`countries`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 246,
"cost": 50.2,
"chosen": true
}
]
},
"cost_for_plan": 50.2,
"rows_for_plan": 246,
"semijoin_strategy_choice": [
{
"strategy": "MaterializeScan",
"choice": "deferred"
}
],
"rest_of_plan": [
{
"plan_prefix": [
"`countries`"
],
"table": "`cities`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 246.2,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 35609,
"cost": 1.75e6,
"chosen": false
}
]
},
"cost_for_plan": 345.4,
"rows_for_plan": 246,
"semijoin_strategy_choice": [
{
"strategy": "MaterializeScan",
"recalculate_access_paths_and_cost": {
"tables": [
{
"table": "`cities`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 246.2,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 35609,
"cost": 1.75e6,
"chosen": false
}
]
}
}
]
},
"cost": 445.8,
"rows": 1,
"duplicate_tables_left": true,
"chosen": true
},
{
"strategy": "DuplicatesWeedout",
"cost": 396.8,
"rows": 1,
"duplicate_tables_left": false,
"chosen": true
}
],
"chosen": true
}
]
},
{
"plan_prefix": [
],
"table": "`cities`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 47478,
"cost": 9656.6,
"chosen": true
}
]
},
"cost_for_plan": 9656.6,
"rows_for_plan": 47478,
"semijoin_strategy_choice": [
],
"pruned_by_cost": true
},
{
"final_semijoin_strategy": "DuplicateWeedout"
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`cities`.`id` = `countries`.`phonecode`)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`countries`",
"attached": null
},
{
"table": "`cities`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"creating_tmp_table": {
"tmp_table_info": {
"row_length": 6,
"key_length": 4,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 2796202
}
}
},
{
"table": "`countries`",
"access_type": "table_scan"
},
{
"table": "`cities`"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
最新文章
- java package一些试验
- PhpStorm创建Drupal模块项目开发教程（3）
- Access SQL中Left Join、Right Join和Inner Join的使用
- MongoDB 相关下载
- javascript获取host
- Tomcat数据源
- Android app内存管理的16点建议
- .net c#获取自定义Attribute
- 前序遍历构造已知二叉树(Java)
- linux系统mysql主从配置
- windows下简单的缓冲区溢出
- cf1107e uva10559区间dp升维
- Javascript实现MD5加密
- mysql 8.0 ~ 安装
- ubuntn 安装python3 及 django及pip3
- Maven学习第1期---Maven简单介绍
- 一则Gedit死机引起的错误之解决
- CCO2017 Vera and Trail Building 构造+图论
- SQLServerException:将截断字符串或二进制数据的解决方法
- IIS测试环境搭建