Optimizing MySQL join_buffer_size and sort_buffer_size for Better Performance

SELECT @@join_buffer_size;

SELECT @@sort_buffer_size;

join_buffer_size is used when the join type is ALL, index, range, or index_merge; each table participating in a join requires its own join buffer. The system default is 128KB.

sort_buffer_size is the buffer used when the server sorts data. It is allocated per thread, so multiple concurrent sorting threads each have their own buffer. The default size is 2MB.

Optimization recommendations : increase join_buffer_size to around 1MB, or up to 2MB if memory allows. Set sort_buffer_size to about 4MB for most workloads; if sorting operations are large and memory is sufficient with moderate concurrency, you can increase it further.

Note that each thread creates its own independent buffer rather than sharing a system‑wide buffer, so setting these values too high can cause overall memory shortage.