Comments on: Use of join buffer is now visible in EXPLAIN http://s.petrunia.net/blog/?p=18 Random observations made while working on MySQL / MariaDB query optimizer Thu, 09 Sep 2010 04:21:29 +0000 http://wordpress.org/?v=2.0.12-alpha by: ever027 http://s.petrunia.net/blog/?p=18#comment-68994 Fri, 18 Jun 2010 01:07:11 +0000 http://s.petrunia.net/blog/?p=18#comment-68994 you said "tbl_x is not inner w.r.t. some outer join", what is the meaning of "w.r.t."? you said “tbl_x is not inner w.r.t. some outer join”,
what is the meaning of “w.r.t.”?

]]> by: sergey http://s.petrunia.net/blog/?p=18#comment-36984 Wed, 26 Nov 2008 22:42:05 +0000 http://s.petrunia.net/blog/?p=18#comment-36984 > Are there any hints for properly sizing the @@join_buffer_size variable relative to the table being buffered? There are many factors in play: - join buffer stores only columns used by the query; - join buffer isn't actually used to buffer tables. As the picture above shows, join buffer stores (tbl1.*, tbl2*) pairs, or more precisely, (used_columns(tbl1), used_columns(tbl2)) pairs that satisfy the part of the WHERE clause that refers to tbl1 and tbl2. So it doesn't directly depend on the table size. If you have very big tables but highly selective conditions in the WHERE clause, you are in the same situation as those with small tables and conditions with poor selectivity. To get a rough estimate, I'd calculate how many record combinations the join is going to enumerate (product of "rows" columns in EXPLAIN output), and divide that by the number of tables in the join to account for records being filtered out. This roughly gives the maximum useful size. The speedup obtained from using bigger join buffer tends to diminish as buffer size increases, so you only need to set it to a reasonably big value. > Are there any hints for properly sizing the @@join_buffer_size variable relative to the table being buffered?

There are many factors in play:
- join buffer stores only columns used by the query;
- join buffer isn’t actually used to buffer tables. As the picture above shows, join buffer stores (tbl1.*, tbl2*) pairs, or more precisely, (used_columns(tbl1), used_columns(tbl2)) pairs that satisfy the part of the WHERE clause that refers to tbl1 and tbl2.

So it doesn’t directly depend on the table size. If you have very big tables but highly selective conditions in the WHERE clause, you are in the same situation as those with small tables and conditions with poor selectivity.

To get a rough estimate, I’d calculate how many record combinations the join is going to enumerate (product of “rows” columns in EXPLAIN output), and divide that by the number of tables in the join to account for records being filtered out. This roughly gives the maximum useful size.

The speedup obtained from using bigger join buffer tends to diminish as buffer size increases, so you only need to set it to a reasonably big value.

]]> by: Robert http://s.petrunia.net/blog/?p=18#comment-36796 Sat, 22 Nov 2008 08:01:34 +0000 http://s.petrunia.net/blog/?p=18#comment-36796 Are there any hints for properly sizing the @@join_buffer_size variable relative to the table being buffered? Are there any hints for properly sizing the @@join_buffer_size variable relative to the table being buffered?

]]> by: Introducing MySQL Visual Explain at Xaprb http://s.petrunia.net/blog/?p=18#comment-6058 Sun, 29 Jul 2007 18:14:31 +0000 http://s.petrunia.net/blog/?p=18#comment-6058 [...] MySQL themselves, and many community members, have sometimes discussed the need for a tree view of EXPLAIN. I sincerely hope they implement that feature and make this little tool obsolete in future versions of MySQL. I also understand MySQL is trying to add more information to EXPLAIN. Currently it’s not possible to get a complete query execution plan from EXPLAIN, because it doesn’t show you everything the server does while executing the query. If MySQL adds information, I’ll update this tool. I have a feature request pending to show when a GROUP BY happens, for example. [...] […] MySQL themselves, and many community members, have sometimes discussed the need for a tree view of EXPLAIN. I sincerely hope they implement that feature and make this little tool obsolete in future versions of MySQL. I also understand MySQL is trying to add more information to EXPLAIN. Currently it’s not possible to get a complete query execution plan from EXPLAIN, because it doesn’t show you everything the server does while executing the query. If MySQL adds information, I’ll update this tool. I have a feature request pending to show when a GROUP BY happens, for example. […]

]]> by: Sergey Petrunia’s blog » EXPLAIN: "Using join cache" renamed to "Using join buffer" http://s.petrunia.net/blog/?p=18#comment-4791 Sun, 10 Jun 2007 01:13:11 +0000 http://s.petrunia.net/blog/?p=18#comment-4791 [...] 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. [...] […] 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. […]

]]>