0

I remember I read about this once. Is it really faster to list all desired columns instead of simply using * even if all columns are needed?

Thanks, Martti Laine

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Martti Laine
  • 12,655
  • 22
  • 68
  • 102
  • 1
    You shouldn't not use `*` performance reasons, you should not use it for maintainability reasons (i.e. you don't want your query to break if someone does an ALTER TABLE somewhere down the line) – Billy ONeal Jan 01 '11 at 17:15
  • 1
    See http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful for performance *and other reasons*. – BoltClock Jan 01 '11 at 17:16
  • 1
    No difference but listing columns explicitly is more defensive. If a new BLOB column is added for example you probably don't want this automatically included in your query. I've seen it argued that listing columns explicitly might be slightly more efficient as it avoids the overhead of looking up column names in the system tables. I doubt this is true though as even when listed explicitly the parser still needs to verify they exist. – Martin Smith Jan 01 '11 at 17:32
  • On top of what has been said already, in my experience you almost never want *all* of the columns. I believe it is a good habit to explicitly list what the query is supposed to return, so that down the road (say query optimization) everyone knows that the query really should return everything. – Chris Pitman Jan 01 '11 at 17:50
  • And I have seen queries in forgotten places where really all was intended. Having the * there saved them from being broken afterwards. – Joshua Jan 01 '11 at 21:38

5 Answers5

2

I have never, never seen the debate about select column-list vs. select * be about performance of the query when the results are exactly the same.

The question should always be, when deciding between the forms, which is more maintainable, and the answer depends on turn on the query's use.

Joshua
  • 40,822
  • 8
  • 72
  • 132
1

It depends. If you are selecting only 3 columns while your tables has 100 columns. Than it's faster.

If you select all columns anyway, than it's simply a different notation.

Wolph
  • 78,177
  • 11
  • 137
  • 148
1

As long as you use all the fields in the table, then there is no significant performance difference between using * and listing all the fields.

The difference comes when you change the table layout in some way. If you add another field, the query using * will automatically start returning that field also, eventhough it's not going to be used. This will increase the overhead, and potentially make the code fail when the data in a record becomes larger than the buffer used to fetch records.

Also listing the fields means that they are returned in the order specified. If you change the order of the fields in the table, the query using * will also change the order in which the fields are returned, which may cause problems in your code. For example, blob fields (e.g. text) have to be retrieved in the order that they are returned, or the values are discarded.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
0

One thing that makes it possibly a lot faster is that when you give the fields, you also give the order. Otherwise I need to retreive the field either by string, or first get he orginal. I simply dont know whether "fieldX" is the first or 10th field.

This can result in either more complicated code, or a lot slowdown (if one has the string lookup within the loop).

TomTom
  • 61,059
  • 10
  • 88
  • 148
0

The real performance difference comes when you the columns that you select are all available through an index. In that case MySQL (and several other DBMS) will do an "index only select" which might a lot faster than doing an index-based select from the table itself.

Additionally the overhead in sending unneeded data over the wire will of course slow down retrieval as well.

And finally is simply good coding practice to only select the data you need.