0

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

I recall reading a number of years ago that in the scenario where you wanted to select everything from a MySQL table it was more efficient and better practice to specify every column rather than use the lazy and less-efficient SELECT * approach.

I am struggling to find any evidence of this online, so i'm not sure if it applies with newer versions of MySQL and PHP.

Would it be better to specify every column in my SELECT rather than using SELECT *?

SELECT * FROM golf_course WHERE id = 2;
Community
  • 1
  • 1
crmpicco
  • 16,605
  • 26
  • 134
  • 210

3 Answers3

2

Yes.

  • Your query will be easier to understand
  • You will only select the columns you need
  • If you specify all columns, it will be easier to remove those you don't want later but if you didn't and you want to do later, you will need to type all the columns
  • You'll be able to arrange the columns the way you want and reference them in your code even if more columns are added to the table later
  • You can easily add computation, concatenation
  • and more

Performance-wise, I am not sure

codingbiz
  • 26,179
  • 8
  • 59
  • 96
  • also, the column order is not defined. changes (additions) to the table will affect your code. – Randy Jun 12 '12 at 12:46
  • Obviously making the code understandable is a concern, but I am more concerned with the performance. If I have _many_ instances of `SELECT *` in my codebase will this effect the performance of my application. I understand that it is not good practice, but I am dealing with legacy code and certainly I would not write a `SELECT *` query and dump it in some PHP. – crmpicco Jun 12 '12 at 12:48
  • @Randy I was previously under the impression that the benefits would be more performance-related? Are there any performance gains from constructing basic queries in this way? – crmpicco Jun 12 '12 at 13:12
  • Performance will ONLY make a difference if it is a column based storage engine. In row based storage engines, it still reads the entire row from disk. – Craig Trombly Jun 12 '12 at 13:56
  • i think the overhead of looking up the column list will be minimal - and if you are actually using all the columns in the result set, then no difference in what is returned - however: as soon as you don't need one column, you are paying more to retrieve it anyway – Randy Jun 12 '12 at 14:11
2

Select * is inherently less efficient because the database has to look up the columns. Further if you have even one join you are sending unnecessary repeated data which is wasteful of database and network resourses, particularly if you do it on every query. Finally, select * doesn't specify the order of the columns, so if someone foolish drops and recreates the table with the columns in a different order, you may suddenly have your Social security number showing up in your first name column on the form or report. And if someone adds a column that you don't want displayed everywhere (say for auditing purposes or notes about the customer that you don't want the customer to ever see) you are in trouble. Further, if you do add columns, you need to determine where they should show up anyway and why not just have them willy nilly show up everywhere. Select * is an extremely bad SQL antipattern.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

IMHO, calling a SELECT * will have to read all fields where are calling only the required fields will be more efficient. And when you are querying a larger database the performance may be affected using SELECT *

chuplu
  • 164
  • 1
  • 14
  • Yes, it's clear that if you have 10 fields in tableX and only need 5 then you specify the 5. But my question is if you have 10 and you need 10 - should you specify the 10 or just `SELECT *`? – crmpicco Jun 12 '12 at 13:16
  • `SELECT *` would be appropriate for that case. – Marcus Recck Jun 12 '12 at 13:18
  • 1
    @marcus, no it wouldn't, select * should not ever be used in production queries, it is a SQl antipattern. The database still has to waste time looking up the columns. – HLGEM Jun 12 '12 at 13:37