begin;
create temporary table last_10000 (o_id bigint, key (o_id));
insert into last_10000
	select o_id from tpcw_orders
	order by o_date desc limit 10000;

create temporary table last_buyers (o_c_id bigint, key (o_c_id));
insert into last_buyers
	select o_c_id from tpcw_order_line
		join tpcw_orders on ol_o_id = o_id
	where ol_o_id in (select o_id from last_10000)
	and ol_i_id = '896'
	group by o_c_id;

explain
select ol_i_id, sum(ol_qty) from tpcw_order_line
	where ol_o_id in (select o_id from tpcw_orders
		where o_id in (select o_id from last_10000)
		and o_c_id in (select o_c_id from last_buyers))
	and ol_i_id <> '896'
	group by ol_i_id
	order by sum(ol_qty) desc, ol_i_id limit 5;

explain
select ol_i_id, sum(ol_qty) from tpcw_order_line,
	(select o_id from tpcw_orders
	        where o_id in (select o_id from last_10000)
			and o_c_id in (select o_c_id from last_buyers)) as O
	where ol_o_id = o_id
	and ol_i_id <> '896'
	group by 1
	order by 2 desc, ol_i_id limit 5;

-- explain
select ol_i_id, sum(ol_qty) from tpcw_order_line,
	(select o_id as o_id1 from tpcw_orders,
		(select o_id as o_id2 from tpcw_orders
			order by o_date desc limit 10000
		) as o2,
		(select o_c_id as o_c_id1 from tpcw_order_line,
			(select o_id, o_c_id from tpcw_orders 
				order by o_date desc limit 10000) as o3
			where ol_o_id = o3.o_id
			and ol_i_id = '896'
			group by 1
		) as c1
		where o_id = o_id2
		and o_c_id = c1.o_c_id1
	) as o1
	where ol_o_id = o1.o_id1
	and ol_i_id <> '896'
	group by 1
	order by 2 desc, 1 asc limit 5;

drop temporary table last_10000;
drop temporary table last_buyers;
commit;
