|
When a materialized derived table is joined using an equality predicate such that one of his parts is a field of the materialized table then hash join algorithm should not be used, because in this case a hash key is created for the table by which it is supposed to be accessed.
No we see that is not so, for example, for the following test case:
CREATE TABLE t1 (
|
id int NOT NULL,
|
amount decimal DEFAULT NULL,
|
PRIMARY KEY (id)
|
) ENGINE=MyISAM;
|
|
CREATE TABLE t2 (
|
id int NOT NULL,
|
name varchar(50) DEFAULT NULL,
|
PRIMARY KEY (id)
|
) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES
|
(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
|
(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);
|
|
INSERT INTO t2 VALUES
|
(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
|
(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);
|
|
set join_cache_level=4;
|
|
EXPLAIN
|
SELECT t2.id,t2.name,t.total_amt
|
FROM t2
|
LEFT JOIN
|
(SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
|
ON t2.id=t.id
|
WHERE t2.id < 3;
|
j
We see that hash join algorithm is applied for this query:
MariaDB [test]> EXPLAIN
|
-> SELECT t2.id,t2.name,t.total_amt
|
-> FROM t2
|
-> LEFT JOIN
|
-> (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
|
-> ON t2.id=t.id
|
-> WHERE t2.id < 3;
|
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
|
| 1 | PRIMARY | t2 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
|
| 1 | PRIMARY | <derived2> | hash_ALL | key0 | #hash#key0 | 5 | test.t2.id | 8 | Using join buffer (flat, BNLH join) |
|
| 2 | DERIVED | t1 | index | NULL | PRIMARY | 4 | NULL | 8 | |
|
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
|
|