Any disadvantages of "SQL_BUFFER_RESULT" for MySQL SELECT?

I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues.

It seems to be a good option to use.

However I cannot seem to find any disadvantages about it. Is it an option which should be used most of the time?

SQL_BUFFER_RESULT creates a temporary table on the server for every result set. This is not a temporary table like CREATE TEMPORARY TABLE - it is an implicit temporary table as would be created when using a GROUP BY clause or with a subquery. As such, all the same rules apply.

First, let's talk the problem that SQL_BUFFER_RESULT is designed to solve:

When the client requests data from the server, until the entire result set has been transmitted to the client the query is still "running" and some locks may still be held. While the data is being transmitted, it will appear in the Sending data state. It is library dependent on whether the client just gets all the data at once when the query is executed, or if it trickles in as you select rows, but an example of a problem is as follows:

resultset = conn["SELECT * FROM bigtable"]
resultset.each do |row|
  data[:value] = row[:value]
  sleep 10 # do something expensive here
end

Obviously contrived, but in the case above if there are 1000 rows, the query will still be actively running for 10,000 seconds. That may seem far fetched, but a lot of applications have "think time" in-between fetching each row because they do some processing. This is "a very bad thing to do." Another case where this type of trickle effect could occur is with a large result set over a slow connection. Ultimately, the problem is the trickling of data to the client causing the query to remain in an active state. SQL_BUFFER_RESULT resolves this problem by buffering the result into a temporary table first, which makes the query end faster, thus releasing all of its locks (what locks?). The result set is then fed to the client from the temporary table rather than the query itself.

That sounds great!

But...

• Temporary tables take up memory and other resources on the server. Lots of temporary tables == lots of resources.

• Tables that exceed the minimum of tmp_table_size or max_heap_table_size will end up being converted to on-disk temporary tables, which means additional I/O. It also means that your queries take longer, as once the size of the MEMORY table reaches tmp_table_size, it is then converted to an on-disk MyISAM table.

• BLOB/TEXT fields cannot be stored as in-memory temporary tables (the MEMORY storage engine does not support them), thus will always be created on disk.

• creating temporary tables is expensive, especially when they are on-disk.

• Consider that one of the first things we try to optimize away when looking at an explain plan is "Using temporary." SQL_BUFFER_RESULT literally makes every query include "Using temporary", (nearly) all the time. My little bit of testing showed that there were cases where MySQL did not use a temporary table even with this hint, but they were limited (single row lookups on primary key seemed to be the only case).

Here are a few examples to show the effect:

```
-- unindexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select * from actor where first_name = 'THORA'\G
1. row
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 203
Extra: Using where
1 row in set (0.00 sec)

-- unindexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where first_name = 'THORA'\G
1. row
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 203
Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- indexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select * from actor where last_name = 'TEMPLE'\G
1. row
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 4
Extra: Using where
1 row in set (0.00 sec)

-- indexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where last_name = 'TEMPLE'\G
1. row
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 4
Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- primary key lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where actor_id = 200\G
1. row
id: 1
select_type: SIMPLE
table: actor
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows:

StackExchange Database Administrators Q#19803, answer score: 10