Server System Variables 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 a Block Nested-Loop or Batched Key Access algorithm 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 the session setting to a larger value only in sessions that are doing large joins, or change the setting on a per-query basis by using a  SET_VAR  optimizer hint (see Section 8.9.3, “Optimizer Hints” ). Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it. When Block Nested-Loop is used, a larger join buffer can be beneficial up to the point where all required columns from all rows in the first table are stored in the join buffer. This depends on the query; the optimal size may be smaller than holding all rows from the first tables. When Batched Key Access 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 is made 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.7, “Nested-Loop Join Algorithms” . For information about Batched Key Access, see Section 8.2.1.12, “Block Nested-Loop and Batched Key Access Joins” . 812 • keep_files_on_create System Variable join_buffer_size Scope Global, Session Dynamic Yes SET_VAR  Hint Applies Yes Type Integer Default Value 262144 Minimum Value 128 Maximum Value (Windows) 4294967295 Maximum Value (Other, 64-bit platforms) 18446744073709547520 Maximum Value (Other, 32-bit platforms) 4294967295 Block Size 128 Command-Line Format --keep-files-on-create[={OFF|ON}] System Variable keep_files_on_create Scope Global, Session Dynamic Yes SET_VAR  Hint Applies No