Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.23
-
None
Description
System settings:
join_cache_level = 8
optimizer_switch join_cache_hashed=on
Tables definition:
CREATE TABLE IMPTest1 (id INT NOT NULL, is_ready INT NOT NULL)
|
ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
|
 |
CREATE TABLE IMPTest2 (id INT NOT NULL, is_ready INT NOT NULL)
|
ENGINE=InnoDB DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`;
|
Queries:
EXPLAIN FORMAT=JSON
|
SELECT * FROM IMPTest1 INNER JOIN IMPTest2 ON IMPTest1.id = IMPTest2.id;
|
Result:
{
|
"query_block": { |
"select_id": 1, |
"table": { |
"table_name": "IMPTest2", |
"access_type": "ALL", |
"rows": 8, |
"filtered": 100 |
},
|
"block-nl-join": { |
"table": { |
"table_name": "IMPTest1", |
"access_type": "hash_ALL", |
"key": "#hash#$hj", |
"key_length": "4", |
"used_key_parts": ["id"], |
"ref": ["kav_ilin.IMPTest2.id"], |
"rows": 16, |
"filtered": 100 |
},
|
"buffer_type": "flat", |
"buffer_size": "100Mb", |
"join_type": "BNLH", |
"attached_condition": "imptest1.`id` = imptest2.`id`" |
}
|
}
|
}
|
Update query
EXPLAIN FORMAT=JSON
|
UPDATE IMPTest1, IMPTest2 SET IMPtest1.is_ready = 1 |
WHERE IMPTest1.id = IMPTest2.id;
|
Result:
{
|
"query_block": { |
"select_id": 1, |
"table": { |
"table_name": "IMPTest2", |
"access_type": "ALL", |
"rows": 8, |
"filtered": 100 |
},
|
"table": { |
"table_name": "IMPTest1", |
"access_type": "ALL", |
"rows": 16, |
"filtered": 100, |
"attached_condition": "imptest1.`id` = imptest2.`id`" |
}
|
}
|
}
|
I expected that the database engine would use hashed join on running the second query.