Performance of Select (pk) vs Select(*) with order by

[Alexander Hristov]

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