Select * and the query optimizer

Question

I have a very simple MySQL table with some very weird behavior. Incidentally the weird behavior is exactly what I want it to do, but I don't want to put this into production not know why its doing what its doing.

Anyways, I've got a table created like so:

Create table `raceTimes` (
    `userID` mediumint(8) unsigned,
    `time` time,
    primary key (`userID`),
    key `idx_time` (`time`)
) engine=InnoDB default charset=utf8;

Now when I do a Select * from raceTimes query, I get a result set like this:

mysql> Select * from raceTimes;
+--------+----------+
| userID | time     |
+--------+----------+
|     14 | 12:37:46 |
|      6 | 12:41:11 |
|      5 | 12:48:45 |
|     13 | 12:55:46 |
|     10 | 13:13:37 |
|      9 | 13:40:37 |
|     17 | 15:30:44 |
|     18 | 15:46:58 |
|      3 | 16:16:45 |
|      8 | 16:40:11 |
|      7 | 16:41:11 |
|      4 | 16:48:45 |
|     16 | 20:30:44 |
|     15 | 20:37:44 |
|      1 | 21:00:00 |
|      2 | 21:16:00 |
|     11 | 23:13:37 |
|     20 | 23:14:58 |
|     19 | 23:46:58 |
|     12 | 23:55:46 |
+--------+----------+

Notice that the result set is order based on the times, from lowest to highest. Ok, so that is exactly what I want the table to do since I'm trying to use this for a leaderboard in a game. When I run explain on my query I get this:

mysql> explain select * from raceTimes;
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | raceTimes  | index | NULL          | idx_time | 4       | NULL |   20 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+

So far everything is great. I'm getting back a sorted result set due to the idx_time index being sorted (as indexes are) and to that end I'm hitting on an index. Now for the weird behavior.

From what I've read, the primary key is indexed by default and is supposed to be the fastest index when querying a table. Yet its not being used. My guess for this is that the idx_time index is smaller than the primary key index, due to it being a time type as opposed to a mediumint(8) type. But it's just a guess.

Now if I create an identical table to the one I created above, but omit the primary key, like so:

Create table `raceTimes2` (
    `userID` mediumint(8) unsigned,
    `time` time,
    key `idx_time` (`time`)
) engine=InnoDB default charset=utf8;

then the result set isn't sorted on the time column this go. This behavior holds even if I tell it to specifically use the idx_time index in my query. Also, if I do an explain on the query I get this:

mysql> explain select * from testTable6 use index(`idx_time`);
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | raceTimes2 | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

So what I'm trying to find out is what's going on behind the scenes. Why does it appear that if I've got a primary key and another index, I can get a result set sorted on the index without even trying and why does the query optimizer go with that other index rather than the primary key index?

Answer 1

As Gordon noted, you should not depend on the natural order of the result set. The reason for the results you are getting is the following:

In the first case MySQL executes the query only by using the idx_time index, without opening the actual table. This is possible when all the columns you are using are in the index (the primary key of an InnoDB table is always appended to the end of every index, so your index is actualy (time,userID) behind the scenes). The results are ordered by time because this is the actual order in the time index.

In the second case the column userID is not part of any index and MySQL has to do a regular table scan to fetch the result. "use index(idx_time)" in this case does nothing because there is no WHERE clause using the time column.

Edit:
It applies only when there is a choice, but if its not possible to use the index specified in USE INDEX MySQL will not use any index on that table for searching (WHERE/ON clause) and will read the entire table. So you should be very careful when using index hints.
Also a row in explain with type='index' means that all the rows in the table will be read, and is almost as bad as type='ALL'.

You should check out the MySQL manual on index hints and explain output.