[MySQLD] join_buffer_size

join_buffer_size

Property	Value
Command-Line Format	--join-buffer-size=#
System Variable	join_buffer_size
Scope	Global, Session
Dynamic	Yes
Type (Other, 64-bit platforms)	integer
Type (Other, 32-bit platforms)	integer
Type (Windows)	integer
Default Value (Other, 64-bit platforms)	262144
Default Value (Other, 32-bit platforms)	262144
Default Value (Windows)	262144
Minimum Value (Other, 64-bit platforms)	128
Minimum Value (Other, 32-bit platforms)	128
Minimum Value (Windows)	128
Maximum Value (Other, 64-bit platforms)	18446744073709547520
Maximum Value (Other, 32-bit platforms)	4294967295
Maximum Value (Windows)	4294967295

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.

When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.

The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).

For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.