Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

share|improve this question
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. –  a_horse_with_no_name Jul 27 at 11:20
    
Thanks, horse. That was in fact the issue. –  dotancohen Jul 27 at 12:33
8  
The ORDER BY NUMBER syntax is quite error prone. –  usr Jul 27 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 at 4:06

2 Answers 2

up vote 26 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
1  
Sometimes the trickiest questions are those which are just staring us in the face. Thanks, Michael! –  dotancohen Jul 27 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 at 12:05
2  
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 at 16:49
    
I assume you have seen this? sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/… –  Max Vernon Jul 28 at 17:10

Your Answer

 
discard

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.