Consider the join_buffer_size parameter in MySQL Server. Which two statements are true about the join buffer?

Question

Options

• AThe value should be increased if the client performs several SELECT operations.
• BThe join buffer is set per connection.
• CThe join buffer is used to process sorts when complex joins are being performed.
• DThe value should be increased from the default if the query joins large rows without using an index.
• EThe join buffer is global and can be changed only by restarting the server.

• A
  6% (3)
• B
  2% (1)
• C
  80% (40)
• E
  12% (6)

Explanation

Options C and D are correct because the join buffer serves two key roles: it is used during complex joins to handle intermediate row sorting/ordering operations (C), and its default value is typically insufficient when queries join large rows across unindexed columns, requiring manual increases to avoid performance degradation (D).

A is wrong because join_buffer_size targets specifically JOIN operations lacking index access - simply running more SELECT queries doesn't consume join buffer memory unless those queries involve non-indexed joins. B is wrong because join_buffer_size is both a global and session-level (per-connection) variable; calling it purely "per connection" is incomplete and misleading. E is wrong because join_buffer_size is a dynamic variable - it can be changed at runtime (SET GLOBAL join_buffer_size = ...) without any server restart, and can also be set at the session level.

Memory tip: Associate the join buffer with "JOINs without INDEXes on large rows" - if you see a slow full-scan join, think D. For C, remember that the buffer must order/sort rows from one table before probing the next, making sort-like processing part of complex join execution.

No community discussion yet for this question.