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

[Alexander Hristov]

Please correct me if I'm wrong - I'm assuming that an index is a structure (btree for InnoDB tables, so it's sorted) that allows the server to locate the pks of records matching a specific value in -at worst- logarithmic time. MySQL *is* using the index, at least so it says in

EXPLAIN select * from customers order by birthdate

+----+-------------+-----------+-------+---------------+-----------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+------+--------+-------+
| 1 | SIMPLE | customers | index | NULL | birthdate | 4 | NULL | 100838 | |
+----+-------------+-----------+-------+---------------+-----------+---------+------+--------+-------+

So for locating the last 20 rows MySQL has to transverse the tree and retain the last 20 visited nodes (no sorting needed). This alone should be quite faster than having to sort 100,000 records to determine the last 20 (although, if MySQL is really clever, no sorting would really be needed for such a query - only visiting all records and comparing the current one with the lowest value from a sorted set of the 20 highest values retrieved so far - in this case it's probably true that an index is only slightly better for this number of records)

But anyway, that is not the issue I'm having.

The problem is why select * is so slow compared to select id (id is the pk). I assume that an index on birthdate stores also the PK of the main record. So the only difference really between ( select * ) and (select pk ) is that an aditional search must be performed in the pk index, and this happens after the 20 rows that must be fetched have been determined - a total of 20 searches in the pk index + 20 row retrievals. So why does this take almost 10 minutes? And why oh why does the execution time depend on the starting position?!