MySQL Forums
Forum List  »  Performance

Adding limit clause takes query from 2 seconds to 42 minutes
Posted by: Josh Higham
Date: December 12, 2012 03:53PM

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 |

Options: ReplyQuote


Subject
Views
Written By
Posted
Adding limit clause takes query from 2 seconds to 42 minutes
3142
December 12, 2012 03:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.