Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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. – a_horse_with_no_name Jul 27 '14 at 11:20
Thanks, horse. That was in fact the issue. – dotancohen Jul 27 '14 at 12:33
The ORDER BY NUMBER syntax is quite error prone. – usr Jul 27 '14 at 14:52
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 4:06
@lc., What do you mean? – Pacerier May 6 '15 at 9:23
up vote 31 down vote accepted

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.

share|improve this answer
Sometimes the trickiest questions are those which are just staring us in the face. Thanks, Michael! – dotancohen Jul 27 '14 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.

share|improve this answer
Thank you for the insight. – dotancohen Jul 28 '14 at 12:05
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?… – Max 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 8 '15 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. – ypercubeᵀᴹ Apr 8 '15 at 22:11

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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