MySQL Bugs: #15130: CREATE TABLE ... SELECT SQL_BIG_RESULT looses filesort optimization

Bug #15130	CREATE TABLE ... SELECT SQL_BIG_RESULT looses filesort optimization
Submitted:	22 Nov 2005 11:19	Modified:	27 Jul 2007 16:23
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.16, 4.1.15	OS:	Linux (Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
In cases where 

SELECT SQL_BIG_RESULT a,b,SUM(c),SUM(d) FROM e GROUP BY a,b

uses filesort (and reads rows from filesorted data), 

CREATE TABLE f SELECT SQL_BIG_RESULT ...

uses filesort, but reads rows from table again. This is much more cpu (and in case of low data cache rates - i/o) intensive. 

4.1 does use partial filesort optimization for INSERT INTO ... SELECT SQL_BIG_RESULT ... as well, but this appears to be fixed in 5.0.

Example table has 2 millions 62-byte wide rows, GROUP BY key is int,int, final resultset is 100k rows. 

In case of larger datasets, if tmp_table_size is exceeded, index-less on-disk temporary tables are too expensive, so SQL_BIG_RESULT is used.

How to repeat:
CREATE TABLE f SELECT SQL_BIG_RESULT a,b,SUM(c),SUM(d) FROM e GROUP BY a,b

Suggested fix:
Include columns, required for SELECT into filesort, then use them, without hitting the source table again.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31477

ChangeSet@1.2485, 2007-07-24 18:15:44+04:00, evgen@moonbone.local +3 -0
  Bug#15130: CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT.
  
  When the SQL_BIG_RESULT flag is specified SELECT should store items from the
  select list in the filesort data and use them when sending to a client.
  The get_addon_fields function is responsible for creating necessary structures
  for that. But this function was allowed to do so only for SELECT and
  INSERT .. SELECT queries. This makes the SQL_BIG_RESULT useless for
  the CREATE .. SELECT queries.
  
  Now the get_addon_fields allows storing select list items in the filesort
  data for the CREATE .. SELECT queries.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

SQL_BIG_RESULT had no effect for CREATE TABLE ... SELECT
SQL_BIG_RESULT ... statements.