EXPLAIN: "Using join cache" renamed to "Using join buffer"

As soon as I’ve finished writing this post about “Using join cache”, it was apparent that “Using join cache” is poor wording. First, the corresponding server variable is called @@join_buffer_size, not join cache size, and second, there is really no cache involved.

We’ve had a discussion about how this should be called. Some sources use term Block nested-loops join but we’ve settled on “Using join buffer“. Another change is that we’ve decided to move the note one line down to the table that “does the buffering”. As a result, what was this

mysql> explain select * from t1, t2 where t1.col < 10 and t2.col < 'bar';
+----+-------------+-------+-------+-...-+-------------------------------+
| id | select_type | table | type  |     | Extra                         |
+----+-------------+-------+-------+-...-+-------------------------------+
|  1 | SIMPLE      | t1    | range |     | Using where; Using join cache |
|  1 | SIMPLE      | t2    | range |     | Using where                   |
+—-+————-+——-+——-+-…-+——————————-+

became this:

mysql> explain select * from t1, t2 where t1.col < 10 and t2.col < 'bar';
+----+-------------+-------+-------+-...-+--------------------------------+
| id | select_type | table | type  |     | Extra                          |
+----+-------------+-------+-------+-...-+--------------------------------+
|  1 | SIMPLE      | t1    | range |     | Using where                    |
|  1 | SIMPLE      | t2    | range |     | Using where; Using join buffer |
+—-+————-+——-+——-+-…-+——————————–+

The change was pushed into 5.1.19 tree. MySQL 5.1.18 is the only version that shows “Using join cache”. I’ve also updated the original explanation post to display the final variants of EXPLAIN output.

Posted in EXPLAIN on June 10th, 2007 by spetrunia | |

3 Responses to ' EXPLAIN: "Using join cache" renamed to "Using join buffer" '

Subscribe to comments with RSS or TrackBack to ' EXPLAIN: "Using join cache" renamed to "Using join buffer" '.


  1. on June 10th, 2007 at 4:58 am

    […] UPDATE: * s/Using join cache/Using join buffer/, changed to show the final variants of EXPLAIN output as described here * s/join_buff_size/join_buffer_size/ Starting from 5.1.18, EXPLAIN output may show “Using join cache”, like in this example: […]

  2. Xaprb said,

    on July 24th, 2007 at 2:53 am

    Thanks for writing all these articles. I really appreciate them. I’m going back through them for the third or fourth time trying to learn more!

  3. sergey said,

    on July 27th, 2007 at 3:15 pm

    Thank you. Glad to hear they are of use to somebody.

Leave a reply

You must be logged in to post a comment.