SQL select statement with either ( * ) or (fields names) which performs better?

Question

Possible Duplicate:
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc.

SELECT * FROM tbl1

and

SELECT field1, field2,...... FROM tbl1

how does SQL engine execute the previous two select statements (step by step) and which one performs better?

@Rami: From a performance time perspective? or from an application design performance persepctive? Personally I thought the two variants were identical with respect to execution time. I may be wrong. — Simen S, Mar 10 '11 at 23:43
@Simen S: i originally asked this quest from performance time perspective, but now im asking for both :) — Rami Alshareef, Mar 10 '11 at 23:45
@Simen: not even close; check out Brent's explanation: http://vimeo.com/12510154 at timestamp 10:50 — p.campbell, Mar 10 '11 at 23:45
@p.campbell Yes, It certainly looks like a duplicate. And interestingly: the two appear *not* to be identical from a performance perspective (which I thought). huh! +1 for instantly recognizing the duplicate :-) — Simen S, Mar 10 '11 at 23:46
@Rami: the answer is clear all 'round: specify your columns, do not use `*` for anything other than your own ad-hoc queries. — p.campbell, Mar 10 '11 at 23:47
@Simen: it's one of the most duplicated questions on StackOverflow; I had to choose only one! :) Dig into the answers and discussion on the other highest voted question: [Can Select * Ever Be Justified?](http://stackoverflow.com/questions/3635781/can-select-usage-ever-be-justified) — p.campbell, Mar 10 '11 at 23:47
How about [Why Is Select * Considered Harmful?](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) OMG nailed it with his answer. — p.campbell, Mar 10 '11 at 23:51
@p.campbell Lol: I really only started to "hang out" here a couple of days ago. I've seen som duplicates already, and will be prepared for thisone again. Has anyone at meta.stackoverflow ever suggested a duplicate detection during posting filter? — Simen S, Mar 10 '11 at 23:52
@Simen: indeed, try composing your own question on this topic. Dupes *are* shown to you as you compose your title, so the OP basically just ignored it, I suspect. — p.campbell, Mar 10 '11 at 23:53
@p.campbell: really?! I'll look for that the next time I feel like asking. I've only asked one question. It has 14 views, 1 vote, no answers and sadly no duplicates. I Didn't recognize the duplicate detection feature. — Simen S, Mar 10 '11 at 23:59

score 1 · Answer 1 · edited May 23 '17 at 12:10

1

Have a look in this old Answered post about:

Select * vs Select Column

Regards

edited May 23 '17 at 12:10

Community

1
1

answered Mar 10 '11 at 23:48

Crimsonland

2,194
3
24
42

score 1 · Accepted Answer · answered Mar 11 '11 at 00:04

1

Simple answer: * is slow, named fields are fast.
Fewer named fields are faster.
Smaller datafields are faster than bigger ones.
Never select blobs of you do not have to.

answered Mar 11 '11 at 00:04

Johan

74,508
24
191
319

score 0 · Answer 3 · answered Mar 11 '11 at 00:07

0

In case of MySQL even a column order matters (and its order in table definition).

answered Mar 11 '11 at 00:07

Piotr Salaciak

1,653
1
15
28

SQL select statement with either ( * ) or (fields names) which performs better?

3 Answers3