SQLize Online / PHPize Online  /  SQLtest Online

-- set session optimizer_switch='derived_merge=off,batched_key_access=on,mrr=on,mrr_cost_based=off,hash_join=on,block_nested_loop=off'; set session optimizer_switch='derived_merge=off'; explain analyze with recursive dates as ( select date'2005-07-01' as date union all select date + interval 1 day from dates where date < date'2005-07-31' ) , t0 as ( select store_id , date(rental_date) as rental_date , date(return_date) as return_date from inventory i join rental r using(inventory_id) where r.rental_date >= date'2005-07-01' and r.rental_date < date'2005-08-01' or r.return_date >= date'2005-07-01' and r.return_date < date'2005-08-01' ) , ren as ( select rental_date , count(case when store_id = 1 then 1 end) as c1 , count(case when store_id = 2 then 1 end) as c2 , count(*) as ct from t0 where rental_date >= date'2005-07-01'and rental_date < date'2005-08-01' group by rental_date ) , ret as ( select return_date , count(case when store_id = 1 then 1 end) as r1 , count(case when store_id = 2 then 1 end) as r2 , count(*) as rt from t0 where return_date >= date'2005-07-01'and return_date < date'2005-08-01' group by return_date ) select d.date , ifnull(c.c1,'0') as rented_at_store_1 , ifnull(r.r1,'0') as returned_at_store_1 , ifnull(c.c2,'0') as rented_at_store_2 , ifnull(r.r2,'0') as returned_at_store_2 , ifnull(c.ct,'0') as total_rented , ifnull(r.rt,'0') as total_returned from dates d left join ren c on c.rental_date = d.date left join ret r on r.return_date = d.date order by 1 ; -- select @@optimizer_switch \G show status like '%cost'; /* select count(*) from inventory i join rental r on r.inventory_id = i.inventory_id where r.rental_date >= date'2005-07-01' and r.rental_date < date'2005-08-01' or r.return_date >= date'2005-07-01' and r.return_date < date'2005-08-01' ; select * from rental limit 5; select * from staff limit 5; select * from inventory limit 5; select store_id, count(*) from inventory group by store_id; */