1Z0-908 practice question 22 of 25

Which condition is true about the use of the hash join algorithm?

Full set: 140 questions
Answer options
A: No index can be used for the join. Correct answer
B: The query must access no more than two tables.
C: The smallest of the tables in the join must fit in memory as set by join_buffer_size.
D: At least one of the tables in the join must have a hash index.
Correct Answer:
A. No index can be used for the join.
Explanation

The MySQL optimizer employs a hash join algorithm primarily for equi-joins where no suitable indexes can be used on the join columns. A hash join avoids the performance issues of the block nested-loop algorithm when indexes are absent. The process involves creating an in-memory hash table from the smaller (build) table and then probing this table with rows from the larger (probe) table to find matches. This strategy is chosen by the optimizer precisely because an index-based join, which is generally faster, is not a viable option for the specific join condition.

Why Incorrect

B. The query must access no more than two tables. This is incorrect. Hash joins can be used in queries involving multiple tables. The result of one hash join can serve as the input for a subsequent join with another table.
C. The smallest of the tables in the join must fit in memory as set by joinbuffersize. This is not a strict requirement. While optimal performance is achieved when the build table fits in memory, the hash join algorithm can handle larger tables by spilling data to temporary files on disk.
D. At least one of the tables in the join must have a hash index. This is incorrect. The hash join algorithm creates its own temporary hash table in memory during query execution; it does not rely on or require any pre-existing HASH indexes on the tables being joined.

References

1. MySQL 8.0 Reference Manual

Section 8.2.1.4 Hash Join Optimization:

"MySQL employs a hash join for any query for which each join has an equijoin condition

and for which there are no indexes that can be applied to any join conditions." (This directly supports answer A).

"For a join of multiple tables

hash join can be used for any pair of tables if their join condition is an equi-join." (This refutes option B).

"If the build table becomes too large to fit in memory

the hash join operation writes chunks of it to temporary files on disk..." (This refutes option C).

The section describes the creation of an in-memory hash table from the build table's rows

which confirms that a pre-existing HASH index is not required

refuting option D.

Scroll to Top