Why would SELECT * be magnitudes faster than SELECT foo?

Consider a table of values and hashes, like so:

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| val        | char(9)  | NO   |     | NULL    |                |
| val_hashed | char(50) | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

The following query finishes in 0.00 seconds:

SELECT * FROM hashes ORDER BY 1 DESC LIMIT 1;

However, this query takes 3 min 17 seconds:

SELECT val FROM hashes ORDER BY 1 DESC LIMIT 1;

I see that while the query is running the process list shows it as status Sorting result. The situation is completely reproducible. Note that there is another process performing INSERT operations on the table continuously.

Why would the more specific query take longer to run than the query? I've always believed that queries should be avoided specifically for performance reasons.

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val. Since id is the key it will be indexed and the order by will be a trivial amount of work. To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows.

Change both queries to order by id and I think your execution times will be almost identical.

StackExchange Database Administrators Q#72493, answer score: 35