Bug #56225 Implement MERGE algorithm for views which use UNION ALL
Submitted: 24 Aug 2010 16:59
Reporter: Sveta Smirnova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:5.0, 5.1, 5.6.99, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[24 Aug 2010 16:59] Sveta Smirnova
Description:
Currently merge algorithm can not be used for views which contain UNION ALL, although creating temporary table not always needed when using such views.

See http://forums.mysql.com/read.php?100,56681,56681 for detailed discussion about the problem.

How to repeat:
create table one (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;

create table two (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;

create algorithm=merge view v as
select * from one
union all
select * from two ;

insert into one values(1, 'fdsd');
insert into two values(1, 'fdsd');

flush status;
show status like '%tmp%';
select descr from v where id > 1000 ;
show status like '%tmp%';

Result:

create table one (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;
create table two (
id int(11) default null,
descr varchar(255)
) Engine = MyISAM;
create algorithm=merge view v as
select * from one
union all
select * from two ;
Warnings:
Warning 1354    View merge algorithm can't be used here for now (assumed undefined algorithm)
insert into one values(1, 'fdsd');
insert into two values(1, 'fdsd');
flush status;
show status like '%tmp%';
Variable_name   Value
Created_tmp_disk_tables 0
Created_tmp_files       0
Created_tmp_tables      0
select descr from v where id > 1000 ;
descr
show status like '%tmp%';
Variable_name   Value
Created_tmp_disk_tables 0
Created_tmp_files       0
Created_tmp_tables      2

Suggested fix:
Implement MERGE algorithm for such views.