2

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

I have table about 45 columns, however I need to select 40 of them.

Wouldn't be SELECT * faster than to list every column but those 5 I do not need?

What's faster in this case?

I'm selecting only ONE ROW!

Community
  • 1
  • 1
genesis
  • 50,477
  • 20
  • 96
  • 125
  • I think yes because telling him to select the 40 out of the 45 will make it work more just to exlude the 5 that you don't want. But i've haven't benchmark that... Try it for yourself and see the loading time... – Warface Jul 24 '11 at 23:42
  • 1
    Yep, that's why I think it would be faster – genesis Jul 24 '11 at 23:42
  • 1
    @warface That is not the case. There is nothing special about `SELECT *` that the optimizer *magically* knows the column list. Run `EXPLAIN EXTENDED SELECT * FROM ..; SHOW WARNINGS;` and you will see that the optimizer translates the `SELECT *` into `SELECT ColA, ColB ...` – The Scrum Meister Jul 24 '11 at 23:45
  • @Genesis: This is an ambigous and very vague question. Quality is very low. – Micromega Jul 24 '11 at 23:46
  • @TheScrumMeister: I don't see any SELECT colA in phpMyAdmin. – genesis Jul 24 '11 at 23:47
  • 2
    The waste is obviously whatever is in the five columns per row that you're fetching needlessly, multiplied by the number of rows returned. Given that, the performance drop of using `select *` should be pretty obvious. Is there a part to this question that perhaps we're not seeing? – Tim Post Jul 24 '11 at 23:52
  • @TimPost: Edited: I'm selecting only ONE ROW! – genesis Jul 24 '11 at 23:54
  • Then as @Tim has said, the waste is going to be whatever is in those 5 columns for that single row + whatever might be added later, if you go with the `select *` option. Either way, at this level (one row), *this is premature optimization*. Go with `select *` and change if it *becomes* a performance problem. – Lasse V. Karlsen Jul 24 '11 at 23:55
  • 1
    If you are selecting only 1 row, the collective time we all spent on answering/commenting on this question is a order of magnitude more then any time saved by `SELECT *` vs `SELECT ColA` ... – The Scrum Meister Jul 24 '11 at 23:56
  • 1
    @genesis , Ok .. the waste is now whatever is in the five columns being fetched needlessly, * 1. I see the question here, but how is it sufficiently different [from this one](http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc)? – Tim Post Jul 24 '11 at 23:58

4 Answers4

3

Typing the columns out is faster for the database, typing * is faster for you to type. Pick whichever is more important :-)

Kerrek SB
  • 464,522
  • 92
  • 875
  • 1,084
  • What about putting on every column an index? – Micromega Jul 24 '11 at 23:44
  • 1
    @Jitamaro: could you clarify your question? – Karoly Horvath Jul 24 '11 at 23:45
  • So it's all about lazyness or being determined :P – Warface Jul 24 '11 at 23:46
  • @Warface: Well, if your tables are tiny and your salary is huge, you might be justified in typing `*` and spend time on something else :-) I doubt you'd deserve the huge salary in that case, though! – Kerrek SB Jul 24 '11 at 23:48
  • @Kerrek: It's about knowlege and helping others. See Scrummaster explanation. – Micromega Jul 24 '11 at 23:49
  • @Kerrek: And also if the database uses a b-tree does it make a difference at all if you select 1 or 40 columns? Where is this bottleneck? I can pack as many payload into a leaf without sacrifice time complexity. – Micromega Jul 24 '11 at 23:57
  • @Jitamaro: Well, you weren't being very specific, and you only added the extra assumption that you only want one row recently. Anyway, *by comparison* the 40-col select will be faster than the *-select, but of course there's no telling how much that is in relative terms compared to the absolute time. General good practice is to ask for what you need and nothing else, though, which will always guide you correctly, as opposed to a half-baked "sometimes I can say `*`" rule. – Kerrek SB Jul 25 '11 at 00:00
  • @Kerrek: Maybe you are right, but to me it makes no difference to pick a bigger payload from a tree then a smaller payload because the idea is the same. It's only a habit or good practise to write all column names. – Micromega Jul 25 '11 at 00:06
1

SELECT * should never be used, at least in production applications. It is always slower. Use SELECT * only while in development stage, when it is faster for you. By the way, it is bad code practice. Read about drawbacks here: http://www.pdxperts.com/article-sqlselect.php

jacek
  • 947
  • 1
  • 11
  • 20
  • so even in case I'm selecting all fields, i should use column listing? – genesis Jul 24 '11 at 23:43
  • "It is always slower". Complete nonsense. `SELECT *` is in no way slower than `SELECT listOfAllFieldsHere`. – Lightness Races in Orbit Jul 24 '11 at 23:44
  • Do you have proof for that? I mean any stats telling that listing columns in the SELECT should be fast than * – Warface Jul 24 '11 at 23:44
  • Yes, think about it this way: if you add new columns in the future, would you still need them in your code, where you call for `SELECT *`? If you are sure your table will not be expanded, then go for `*`. But you could never be sure about that, could you? – jacek Jul 24 '11 at 23:47
  • 1
    @tomalak-geretkal How is selecting all the columns via `SELECT *` not slower then selecting only the 40 out of the 45 columns? Transmitting the extra 5 columns - that the OP states he doesn't want - for every row across the network translates into *some* latency. – The Scrum Meister Jul 24 '11 at 23:48
  • @tomalak-geretkal `*` is resolved to column names *anyway*, so it takes time. – jacek Jul 24 '11 at 23:51
  • 1
    Guys... he's right. `SELECT *` isn't **always** slower, it's just never faster. –  Jul 25 '11 at 00:01
  • @bdares: Then he's not right, because he said it's "always slower". "Never faster" is correct. – Lightness Races in Orbit Jul 25 '11 at 15:18
  • @TheScrumMeister: Buffering means that that will _never_ be your bottleneck. – Lightness Races in Orbit Jul 25 '11 at 15:19
  • @Warface: It's common sense. How could it be slower in that instance? The engine still has to look up the fields in the table and verify that the ones you've listed exist; that's no different really from expanding `*` into a list of them all. – Lightness Races in Orbit Jul 25 '11 at 15:19
1

from http://forge.mysql.com/wiki/Top10SQLPerformanceTips

Network Performance Tips:

Minimize traffic by fetching only what you need:

  • Don't use SELECT *

Also think of DB engine allocating result set memory for those 5 extra columns that you don't need and sending it over the wire. Even if there is no performance penalty there is memory overhead, so it is almost always better to select only what you need.

LeffeBrune
  • 3,441
  • 1
  • 23
  • 36
0

The server still has to read pages from disk if it is not in memory, then read from memory to cache and finally to cpu and those data will be on the same page unless you have some really huge columns that you don't want to fetch.

You can reduce the network traffic a little bit (40 vs 45 fields), but I doubt it will make a big difference.

Karoly Horvath
  • 94,607
  • 11
  • 117
  • 176