1Z0-908 practice question 22 of 25
Which condition is true about the use of the hash join algorithm?
Full set: 140 questionsThe 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.
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.
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.
Keep practicing
Take the free 1Z0-908 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other 1Z0-908 practice questions
- Question 2You must run multiple instances of MySQL Server on a single host. Which three methods are…
- Question 3Which two tools are available to monitor the global status of InnoDB locking? (Choose two…
- Question 4Which two MySQL Server accounts are locked by default? (Choose two.)
- Question 5All MySQL Server instances belonging to InnoDB Cluster have SSL configured and enabled. Y…
- Question 6t is a non-empty InnoDB table. Examine these statements, which are executed in one sessio…
- Question 8Examine this command, which executes successfully: $ mysqlrouter --bootstrap user@hostnam…
