MySQL Forums
Forum List  »  Optimizer & Parser

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

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?!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance of Select (pk) vs Select(*) with order by
4207
November 08, 2005 03:53AM


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.