Re: File full error in simple select statement

[Derek McKinnon]

1) 8% does sound reasonable. Yes.
3) (First). Only 200 rows have an amount of null.
2)

EXPLAIN select
w.weekend,
d2.code as family,
sum(ifnull(d2.amount, 0)) as owing
from weekendings w
join dr02 d2 on (w.weekend >= d2.date and d2.t1 <> "g")
or (w.weekend >= d2.weekend and d2.t1 = "g")
group by w.weekend, d2.code;


id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE w \N index WeekEnd WeekEnd 3 \N 791 100.00 Using index; Using temporary
1 SIMPLE d2 \N ALL Dates,t1Index \N \N \N 111505 27.78 Using where; Using join buffer (hash join)


EXPLAIN select weekend, family, sum(amount) as owing
from (
select w.weekend, d2.code as family, d2.amount
from weekendings w
join dr02 d2 on (w.weekend >= d2.date and d2.t1 <> "g")
or (w.weekend >= d2.weekend and d2.t1 = "g")
where d2.code = "smi1" and d2.amount is not null
) x
group by weekend, family
order by weekend, family;


id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE d2 \N ref CODE,DR02,Dates,t1Index CODE 24 const 542 45.00 Using where; Using temporary; Using filesort
1 SIMPLE w \N ALL WeekEnd \N \N \N 791 55.55 Range checked for each record (index map: 0x2)