Re: Adding limit clause takes query from 2 seconds to 42 minutes

[aftab khan]

Please also provide output of the following:

select count(*) 
from player_record pr 
inner join date_curr dc 
on pr.player_id = dc.player_id 
where pr.insert_date < '2012-05-15';

Please note date_curr contains data where pr.insert_date between '2012-05-15' and '2012-05-21'

I think you don't need to join player_record and date_curr when inserting data into date_prev, try this:

insert into date_prev 
select pr.player_id, max(pr.insert_date) as insert_date 
from player_record pr 
where pr.insert_date < '2012-05-15' 
group by pr.player_id limit 0,20000;

and tell us how long did it take?

Please provide EXPLAIN output of the following sql:

EXPLAIN select pr.player_id, max(pr.insert_date) as insert_date 
from player_record pr 
where pr.insert_date < '2012-05-15' 
group by pr.player_id limit 0,20000;