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:


However, this query takes 3 min 17 seconds:


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.

  • 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.
    – user1822
    Jul 27, 2014 at 11:20
  • 8
    The ORDER BY NUMBER syntax is quite error prone.
    – usr
    Jul 27, 2014 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, 2014 at 4:06
  • @lc., What do you mean?
    – Pacerier
    May 6, 2015 at 9: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 7, 2015 at 5:18

2 Answers 2


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.

  • 3
    Sometimes the trickiest questions are those which are just staring us in the face. Thanks, Michael!
    – dotancohen
    Jul 27, 2014 at 12:33

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.

  • 5
    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. Jul 28, 2014 at 16:49
  • I assume you have seen this? sqlblog.org/2009/10/10/…
    – Hannah Vernon
    Jul 28, 2014 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 8, 2015 at 18:20
  • @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. Apr 8, 2015 at 22:11
  • @ypercube, Ah, if only it's as smart as pgsql.
    – Pacerier
    Apr 9, 2015 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.