MySQL Forums
Forum List  »  Optimizer & Parser

Performance of Select (pk) vs Select(*) with order by
Posted by: Alexander Hristov
Date: November 07, 2005 08:54AM

Hello!

I'm stress testing a web application generator that uses MySQL5 and cannot understand a performance issue with a query. My test table - InnoDB - is very simple :

mysql> describe customers;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | varchar(30) | NO | PRI | | |
| name | varchar(60) | YES | | NULL | |
| birthdate | date | YES | MUL | NULL | |
| introduced | datetime | YES | | NULL | |
| starts | time | YES | | NULL | |
| ends | time | YES | | NULL | |
| sex | char(1) | YES | | M | |
| isVIP | tinyint(1) | YES | | 0 | |
| photograph | blob | YES | | NULL | |
| weight | decimal(6,2) | YES | | NULL | |
| profile | text | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+

All fields are filled with randomly generated data. The table has about 100,000 rows , and an index on birthdate. The blob field is null for all rows, the text field is filled with a random text made up of 10 to 500 ascii characters.

Ok, running queries like select id from customers order by birthdate limit xxx,20 takes place -as expected - almost instantly, and it does not depend on the starting position (xxx).

However, select * from customers order by birthdate limit xxx,20 takes a variable time that depends - linearly! - on xxx. For example

limit 10,20 -> 0 sec.
limit 200,20 -> 2,38 sec
limit 500,20 -> 4,86 sec
limit 1000,20 -> 9,39 sec
limit 2000,20 -> 18,59 sec
limit 5000,20 -> 47,98 sec

But what is more interesting is that a simple

select * from customers order by birthdate

takes less time (59,13 sec) to retrieve all 100,000 rows (and print them!) than

select * from customers order by birthdate limit 99980,20

which takes theoretically should retrieve only 20.


Any help or explanations about this behaviour?

Thanks a lot in advance,

Alexander

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance of Select (pk) vs Select(*) with order by
5121
November 07, 2005 08:54AM


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.