How would the view look like?

set optimizer_switch='derived_merge=off';
set optimizer_switch='materialization=on';

drop table if exists t1;
create table t1(a int , b smallint unsigned, c numeric(10) , primary key (a,b)) engine =innodb;
set @b:=1;
insert into t1 select 1 ,@b:=@b+1, 1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;
insert into t1 select 1 ,@b:=@b+1, 1 from t1;

insert into t1 select 2 ,b, 1 from t1 where a=1;
insert into t1 select 3 ,b, 1 from t1 where a=1;
insert into t1 select 4 ,b, 1 from t1 where a=1;
insert into t1 select 5 ,b, 1 from t1 where a=1;
insert into t1 select 6 ,b, 1 from t1 where a=1;
insert into t1 select 7 ,b, 1 from t1 where a=1;
insert into t1 select 8 ,b, 1 from t1 where a=1;
insert into t1 select 9 ,b, 1 from t1 where a=1;

insert into t1 select 12 ,b, 1 from t1 where a=1;
insert into t1 select 13 ,b, 1 from t1 where a=1;
insert into t1 select 14 ,b, 1 from t1 where a=1;
insert into t1 select 15 ,b, 1 from t1 where a=1;
insert into t1 select 16 ,b, 1 from t1 where a=1;
insert into t1 select 17 ,b, 1 from t1 where a=1;
insert into t1 select 18 ,b, 1 from t1 where a=1;
insert into t1 select 19 ,b, 1 from t1 where a=1;

– creating a table to store column b that will be our indice to dynamic column

drop table if exists i1;
create table i1(a int , b smallint unsigned, primary key (a,b))  engine=innodb;
insert into i1 select  a, b from t1 group by a,b;

set global max_allowed_packet=1024*1024*100;
drop table if exists t2;

– injecting t1 to compressed dynamic column
create table t2(a int primary key , dc blob) engine=innodb ;

delimiter //
drop procedure if exists spa //

CREATE PROCEDURE spa()
BEGIN
DECLARE mya BIGINT;
DECLARE MyQuery TEXT;
DECLARE not_found BOOLEAN DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT DISTINCT a FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = TRUE;
SET group_concat_max_len=1024*1024*1024;
OPEN cur1;
SET not_found = FALSE;
REPEAT
FETCH cur1 INTO mya;
BEGIN
DECLARE mya_t2 BIGINT;
DECLARE myb_t2 TEXT;
DECLARE result_not_found BOOLEAN DEFAULT FALSE;
DECLARE cur2 CURSOR FOR
SELECT SQL_BIG_RESULT a,
GROUP_CONCAT(b,',',c,' as DECIMAL')
FROM t1
WHERE a=mya
GROUP BY a;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_not_found = TRUE;
OPEN cur2;
SET result_not_found = FALSE;
REPEAT
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION COMMIT;
FETCH cur2 INTO mya_t2,myb_t2;
SELECT CONCAT(
'INSERT INTO t2 VALUES(',mya_t2,
',' ,
'COMPRESS(COLUMN_CREATE(',
myb_t2,
')))') INTO MyQuery;
SET @insert_query=MyQuery;
START TRANSACTION;
PREPARE stmt FROM @insert_query;
EXECUTE stmt;
COMMIT;
END;
UNTIL result_not_found = TRUE END REPEAT;
CLOSE cur2;
END;
UNTIL not_found = TRUE END REPEAT;
CLOSE cur1;
END//
call spa//
delimiter ;

– recreate t1 from compressed t2
– fixed in MDEV-4292

CREATE VIEW `v2` AS
SELECT U.a,i1.b, COLUMN_GET(U.dc,i1.b as DECIMAL) as c FROM ( SELECT a , UNCOMPRESS(dc) dc FROM t2 )U straight_join i1 on U.a=i1.a where COLUMN_GET(U.dc,i1.b as DECIMAL) IS NOT NULL;

– creating uncompressed table t3 from t2
drop table if exists t3;
create table t3 like t2 ;
insert into t3 select a, uncompress(dc) from t2;

– recreate t1 from uncompressed t3
drop view if exists v3;
CREATE VIEW v3 AS
SELECT t3.a,i1.b,
COLUMN_GET(t3.dc,i1.b as DECIMAL ) as c
FROM t3 INNER JOIN
i1 on i1.a=t3.a
where COLUMN_GET(t3.dc,i1.b as DECIMAL) IS NOT NULL;

– create t4 innodb compression from uncompressed table

drop table if exists t4;
create table t4(a int primary key , dc blob) engine=innodb row_format=compressed ;
insert into t4 select * from t3;

– recreate t1 from compressed t4
drop view if exists v4;
CREATE VIEW v4 AS
SELECT t4.a,i1.b,
COLUMN_GET(t4.dc,i1.b as DECIMAL ) as c
FROM t4 INNER JOIN
i1 on i1.a=t4.a
where COLUMN_GET(t4.dc,i1.b as DECIMAL) IS NOT NULL;

Stephane Varoqui | Senior Consultant EMEA
SkySQL Ab | www.skysql.com Location: Paris -France | Tel : +33 6 95 92 64 01
SkySQL - The first choice in affordable MySQL® Database solutions for the Enterprise and Cloud

It works if you substitute "( SELECT a , UNCOMPRESS(dc) dc FROM t2 )" as another view.

Hi Patryk,

Well in our first test it looks like the conditions have not been push down for internal view or that derived_merge is lost lost .

The idea is not to decompress for every record of i1

I'll ask our client to review again if we have missed something here like an index that would have cause us to miss interpreted that result

Thanks for the investigation so far .

Sandrine, Simon, can provide us an explain of the embedded view test case . Can you also retest on mariadb 10 as it looks like innodb compression is much much better in that release .

Stéphane

Stephane Varoqui | Senior Consultant EMEA
SkySQL Ab | www.skysql.com Location: Paris -France | Tel : +33 6 95 92 64 01
SkySQL - The first choice in affordable MySQL® Database solutions for the Enterprise and Cloud