The latest from the MySQL community

Is your MySQL server doing an extra index scan on queries that need to check a key for matches or NULL? It's easy for this to happen accidentally, but it's also easy to fix, especially in MySQL 5.0 and up. Here's how.

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                         | …

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:

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 join cache is actually not a new feature. It has been …