drop table if exists player_record,date_curr;
create table `player_record` (`player_id` int unsigned not null,`insert_date` date not null,
`samples` mediumint unsigned default null,primary key (`player_id`,`insert_date`),
key `insert_date` (`insert_date`)) engine=myisam;
set @player_id=4;
insert ignore into player_record values(1,'2012-05-15',1),(2,'2012-05-16',1),(3,'2012-05-17',1);
insert ignore into player_record 
select @player_id:=(@player_id + 1) mod 100000,adddate('2012-05-15', interval floor(30*rand()) day),1 
from
player_record a1,player_record a2,player_record a3,player_record a4,player_record a5,
player_record a6,player_record a7,player_record a8,player_record a9,player_record aa,
player_record ab,player_record ac,player_record ad,player_record ae,player_record af
limit 7000000;
select count(*),insert_date from player_record group by insert_date;

create table date_curr (player_id int unsigned not null, insert_date date, primary key player_id (player_id, insert_date)) engine=memory;
insert into date_curr select player_id, max(insert_date) as insert_date from player_record where insert_date between '2012-05-15' and '2012-05-15' + interval 6 day group by player_id;


analyze table player_record;
select count(distinct player_id) from date_curr;
select count(*) from date_curr;

explain select pr.player_id, max(pr.insert_date) as insert_date from player_record pr inner join date_curr dc on pr.player_id = dc.player_id where pr.insert_date < '2012-05-16' group by pr.player_id;
explain select pr.player_id, max(pr.insert_date) as insert_date from player_record pr inner join date_curr dc on pr.player_id = dc.player_id where pr.insert_date < '2012-05-16' group by pr.player_id limit 1;