When I run the explain extended with my query (I deleted creat table part from my query) this is what I got.
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE alltheuniquerecords ALL NULL NULL NULL NULL 13648180 100 NULL
1 SIMPLE withdrawalsonly ALL NULL NULL NULL NULL 2603443 100 Using where; Using join buffer (Block Nested Loop)
alltheuniquerecords
Field Type Null Key Default Extra
Product varchar(50) YES MUL NULL
Date1 datetime YES NULL
No varchar(20) YES NULL
Sex varchar(1) YES NULL
DOB datetime YES NULL
Date2 datetime YES NULL
Date3 datetime YES NULL
PP varchar(10) YES NULL
AV double YES NULL
AV2 double YES NULL
AV3 double YES NULL
AV4 double YES 0
AV5 double YES NULL
AV6 double YES NULL
AV7 tinyint(1) YES 0
withdrawalsonly
Field Type Null Key Default Extra
Product varchar(15) YES MUL NULL
No varchar(15) YES NULL
Date1 datetime YES NULL
Date4 datetime YES NULL
Date6 varchar(3) YES NULL
WAmount double YES NULL
Status varchar(3) YES NULL
PS: I used "describe" get the table info, hope that is ok.
PPS: as you can see my counts are little off.
Edited 6 time(s). Last edit at 02/20/2014 05:18PM by John Doe.