1

I use PHP and MYSQL on a Linux webserver and have usually programmed to select all the field of records matched - ie 'SELECT * FROM table1 where field1 like query'. In some cases (as in producing an on-screen index) I then only use 4 or 5 fields from the returned records which have 50 fields.

I imagine if I want to use, say, 49 fields, then selecting all 50 makes sense; but there may be a point (ratio ?) where selecting only the fields required may be more efficient.

Is there a performance implication selecting all the fields rather than selecting just the fields required ?

user3495662
  • 33
  • 3

1 Answers1

1

Networks are fast, but they're not infinitely fast. There's a performance cost of repeatedly transferring bytes across a network that you don't need. Keep in mind that you probably do this many times per second in a typical application.

Suppose your query requests 50 columns but your app only uses 49. That's a 2% overhead (assuming the unused columns are exactly average in size). If you could reduce the overhead of network transfer of query result sets by 2% with a one-line change to your code, with very little risk, would you do it?

The difference gets even more important if you only use half of the columns in your SELECT * result. Or if some of the columns you don't need are larger than average, like long BLOB/TEXT columns.

There are also other benefits to avoiding SELECT * besides runtime performance.

For example, if someone renames a column in your table, but your code still fetches all the columns with SELECT * and then references columns in the result set by name, you might be confused that your application no longer displays some data, or fails to use the data in calculations. This is hard to debug, or might go unnoticed for months.

Whereas if you explicitly name the columns in your SQL query, and you reference a column that no longer has that name, you'll get a clear error message the very next time the query is run. One good principle of software design is "Fail Fast."

Finding bugs quickly and fixing them promptly helps improve another kind of performance: developer performance.

I wrote about the disadvantages of using SELECT * in a chapter of my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828