materialized_path_tree.sql

drop table cat;
drop table item;
drop table item_cat;

create table if not exists cat (
  cid int(11) not null,
  cpath varchar(10) not null,
  cname varchar(100) not null,
  primary key(cid)
);

create table if not exists item (
  item_id int(11) not null,
  primary key(item_id)
);

create table if not exists item_cat (
  item_id int(11) not null,
  cid int(11) not null,
  primary key(item_id, cid)
);

truncate table cat;

insert into cat(cid, cpath, cname) values
(0, '0', 'root'),
(1, '0.1', 'food'),
(2, '0.1.1', 'restaurant'),
(3, '0.1.2', 'fastfood'),
(4, '0.1.3', 'bar'),
(5, '0.2', 'engineer'),
(6, '0.2.1', 'system engineer'),
(7, '0.2.2', 'network engineer'),
(8, '0.2.3', 'programer')
;

truncate table item;
truncate table item_cat;

delimiter //

create procedure gendata ()
begin
  declare _rownum int;
  set _rownum = 0;
  while _rownum < 100000 do
    insert into item (item_id) values (_rownum);
    insert into item_cat (item_id, cid) values (_rownum, _rownum%9);
    set _rownum = _rownum + 1;
  end while;
  insert into item (item_id) values (100000);
  insert into item_cat (item_id, cid) values (100000, 2);
  insert into item_cat (item_id, cid) values (100000, 3);
end
//

delimiter ;

-- tree
select
  a.cpath
, a.cname
, b.cpath
, b.cname
from
  cat a
  inner join cat b on b.cpath like concat(a.cpath, '%')
order by
  a.cpath
, b.cpath
;

-- count
explain
select
  a.cid
, a.cpath
-- , count(i.item_id)
, count(distinct i.item_id)
from
  cat a
  inner join cat b on b.cpath like concat(a.cpath, '%')
  inner join item_cat c on c.`cid` = b.cid
  inner join item i on i.item_id = c.item_id
group by
  a.cid
, a.cpath
order by
  a.cid
, a.cpath
;