1

I'm a self taught developer and ive always been told not to use SELECT *, but most of my queries require to know all the values of a certain row...

what should i use then? should i list ALL of the properties every time? like Select elem1,elem2,elem3,....,elem15 FROM...?

thanks

Francesco
  • 24,839
  • 29
  • 105
  • 152
  • possible duplicate of [What is the reason not to use select * ?](http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select) – NotMe Sep 21 '10 at 19:34
  • Also: http://stackoverflow.com/questions/128412/sql-query-question-select-from-view-or-select-col1col2from-view – NotMe Sep 21 '10 at 19:35

8 Answers8

4

If you really need all the columns and you're fetching the results by name, I would go ahead and use SELECT *. If you're fetching the row results by index, then it makes sense to specify the column names or else they might not be in the order you expect (especially if the table schema changes).

Nick
  • 11,475
  • 1
  • 36
  • 47
  • I have NEVER had a need to fetch columns by index rather than name. I can't even come up with a valid scenario where you would. – CaffGeek Sep 21 '10 at 19:24
  • Chad, I am glad. I certainly wouldn't choose index access over name access; I just know that there exist APIs that select results by index, and the OP hasn't specified what language or platform he is using to talk to MySQL. – Nick Sep 21 '10 at 19:25
  • 1
    I don't know why you would either, but PHP's [`mysql_fetch_row`](http://ca.php.net/manual/en/function.mysql-fetch-row.php) will return a numerically-indexed array, for an example of this occurring in the wild. – Daniel Vandersluis Sep 21 '10 at 19:28
  • 2
    It's scary the number of apps I've seen where people are using the index instead of column names. +1 in the event that just ONE person reads this and thinks "hmm, maybe that's not a good idea" – NotMe Sep 21 '10 at 19:32
  • @Chad: The numeric index for column reference is called an ordinal, and you're correct--it's available, but I've [yet to see a good reason to use it](http://stackoverflow.com/questions/2253040/benefits-of-using-sql-ordinal-position-notation). – OMG Ponies Sep 21 '10 at 19:42
3

SELECT * FROM ... is not always the best way to go unless you need all columns. That's because if for example a table has 10 columns and you only need 2-3 of them and these columns are indexed, then if you use SELECT * the query will run slower, because the server must fetch all rows from the datafiles. If instead you used only the 2-3 columns that you actually needed, then the server could run the query much faster if the rows were fetch from a covering index. A covering index is one that is used to return results without reading the datafile.

So, use SELECT * only when you actually need all columns.

Vasilis Lourdas
  • 1,179
  • 1
  • 16
  • 35
2

If you absolutely have to use *, try to limit it to a specific table; e.g.:

SELECT t.*
  FROM mytable t
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
1

List only the columns that you need, ideally with a table alias:

SELECT t.elem1,
       t.elem2
  FROM YOUR_TABLE t

The presence of a table alias helps demonstrate what is a column (and where it's from) vs a derived column.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
1

If you are positive that you will always need all the columns then select * should be ok. But my reasoning for avoiding it is: say another developer has another column added to the table which isn't required by your query..then there is overhead. This can get worse as more columns get added.

aeq
  • 10,377
  • 15
  • 45
  • 46
0

The only real performance hit you take from using select * is in the bandwidth required to send back extra columns in your result set, if they're not necessary. Other than that, there's nothing inherently "bad" about using select *.

Matt
  • 782
  • 5
  • 14
  • 1
    Not true. http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select – NotMe Sep 21 '10 at 19:34
0

You might SELECT * from a subquery.

Hammerite
  • 21,755
  • 6
  • 70
  • 91
0

Yes, Select * is bad. You do not state what language you will be using process the returned data. Suppose you receive these records back as an array (not a hash map). In this case, what is in Row[12]? Maybe it was ZipCode when you wrote the app, but guess what happens when someone inserts a field SuiteNumber before ZipCode.

Or suppose the next coder appends a huge blob field to each record. Ouch!

Or a little more subtle: let's assume you're doing a join or subselect, and you have no Text or Blob type fields. MySQL will create any temp files it needs in memory. But as soon as you include a Text field (even TinyText), MySQL will need to create the temp file on disk, and call sort-merge. This won't break the program, but it can kill performance.

Select * is sacrificing maintainability in order to save a little typing.

Guy Gordon
  • 740
  • 6
  • 13