Basic information about the join buffer cache:
The size of each join buffer is determined by the value of the
This buffer is used only when the join is of type
index(in other words, when no possible keys can be used).
A join buffer is never allocated for the first non-const table, even if it would be of type
The buffer is allocated when we need to do a full join between two tables, and freed after the query is done.
Accepted row combinations of tables before the
indexare stored in the cache and are used to compare against each read row in the
We only store the used columns in the join buffer, not the whole rows.
Assume you have the following join:
Table name Type t1 range t2 ref t3 ALL
The join is then done as follows:
- While rows in t1 matching range - Read through all rows in t2 according to reference key - Store used fields from t1, t2 in cache - If cache is full - Read through all rows in t3 - Compare t3 row against all t1, t2 combinations in cache - If row satisfies join condition, send it to client - Empty cache - Read through all rows in t3 - Compare t3 row against all stored t1, t2 combinations in cache - If row satisfies join condition, send it to client
The preceding description means that the number of times table
t3 is scanned is determined as follows:
S = size-of-stored-row(t1,t2) C = accepted-row-combinations(t1,t2) scans = (S * C)/join_buffer_size + 1
The larger the value of
join_buffer_size, the fewer the scans of
join_buffer_sizeis already large enough to hold all previous row combinations, there is no speed to be gained by making it larger.
If there are several tables of join type
index, then we allocate one buffer of size
join_buffer_sizefor each of them and use the same algorithm described above to handle it. (In other words, we store the same row combination several times into different buffers.)