Adding limit clause takes query from 2 seconds to 42 minutes

[Josh Higham]

I am trying to test another performance issue, and to do that I want to create a table with less entries by adding LIMIT to the query. If I execute the query as-is, it takes about 2 seconds to complete, but if I add "LIMIT 1" it takes 42 minutes!

mysql> describe player_record;
+------------------------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------------+------+-----+---------+-------+
| player_id | int(10) unsigned | NO | PRI | NULL | |
| insert_date | date | NO | PRI | NULL | |
| xp | int(10) unsigned | YES | | NULL | |
+------------------------+------------------------+------+-----+---------+-------+
17 rows in set (0.01 sec) (most columns removed)

There are 20 million rows in the player_record table, so I am creating two tables in memory for the specific dates I am looking to compare.

create temporary 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;
create temporary table date_prev like date_curr;
insert into date_prev 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-15' group by pr.player_id limit 0,20000;

date_curr has 216k entries, and date_prev has 105k entries if I don't use a limit.

Here is the explain output:

mysql> 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-15' group by pr.player_id limit 0,20000;
+----+-------------+-------+-------+---------------------+-------------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------+-------------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | pr | range | PRIMARY,insert_date | insert_date | 3 | NULL | 396828 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | dc | ALL | PRIMARY | NULL | NULL | NULL | 216825 | Using where; Using join buffer |
+----+-------------+-------+-------+---------------------+-------------+---------+------+--------+----------------------------------------------+
2 rows in set (0.03 sec)


This is on a system with 24G RAM dedicated to the database, and currently is pretty much idle. This specific database is the test so it is completely static. I did a mysql restart and it still has the same behavior.

Here is the 'show profile all' output, with most time being spent on copying to tmp table.

| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
| Copying to tmp table | 999.999999 | 999.999999 | 0.383941 | 110240 | 18983 | 16160 | 448 | 0 | 0 | 0 | 43 | 0 | exec | sql_select.cc | 1976 |