30

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.

dotancohen
  • 1,085
  • 6
  • 16
  • 27
  • 7
    The first statements most probably uses the primary key index on id to find the first row. The second one needs to sort the complete result on the (un indexed) val column. –  Jul 27 '14 at 11:20
  • 8
    The ORDER BY NUMBER syntax is quite error prone. – usr Jul 27 '14 at 14:52
  • 2
    Adding to your last comment, SELECT * combined with a column index in ORDER BY is obfuscating which column is being sorted - another reason to avoid *s... – lc. Jul 28 '14 at 04:06
  • @lc., What do you mean? – Pacerier May 06 '15 at 09:23
  • @Pacerier I mean the * is not explicit. So saying "give me all the columns and sort by the third one" is about as deterministic as saying "go to the supermarket and tell me how many traffic lights you passed" – lc. May 07 '15 at 05:18
  • @lc. Assuming the tables are dynamic and might/will change in the future. The command is pretty valid for tables that will only be created and dropped and will never ever change. – Pacerier May 24 '15 at 14:55

2 Answers2

35

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.

Michael Green
  • 24,839
  • 13
  • 51
  • 96
8

The performance difference in your query is well explained by MG. I am going to address this:

I've always believed that * queries should be avoided specifically for performance reasons.

select * carries no particular penalties by itself, it is problematic when misused. In a single-table query it works just fine. now join that table to another with 20 columns, and later add joins to 5 other tables with many columns each. NOW it's a problem. So are people who teach broad, band-aid "never do X" without explaining why.

paul
  • 129
  • 1
  • 6
    SELECT * might be an issue even for a single-table query. For example, SELECT * FROM hashes ORDER BY val; will probably do a full table scan and then a sort while SELECT val FROM hashes ORDER BY val; will do only a full index scan, and no sort (assuming an index exists on val). So, it never hurts to select only the results that we need. – ypercubeᵀᴹ Jul 28 '14 at 16:49
  • I assume you have seen this? https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list – Hannah Vernon Jul 28 '14 at 17:10
  • @ypercube, Does that occur even if our select(*) is only used as a sub-select? Since it's an embedded select, Wouldn't MySQL be smart enough to figure out the actual columns that need selecting? – Pacerier Apr 08 '15 at 18:20
  • 1
    @Pacerier mysql optimizer has different levels of "smartness", depending on the version you are using. In gerneal, it was pretty dumb regarding nested subqueries, so whatever you could to help him, it was good. – ypercubeᵀᴹ Apr 08 '15 at 22:11
  • @ypercube, Ah, if only it's as smart as pgsql. – Pacerier Apr 09 '15 at 05:05