SELECT Statement Performance using * versus a List of Field Names

Question

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

In some code I'm reading, all SELECT statements are using a list of field names where all fields in the table are being selected. For example, with a table called Book and fields Author, PublishDate, Pages and Price, the select statement looks like this:

SELECT Author, PublishDate, Pages, Price FROM Book;

All select statements in the application are like this which makes me wonder if there is an increase in performance doing that versus:

SELECT * FROM Book;

I flipped through a MySql book but didn't see anything related to this. Given the maintenance overhead of changing the SELECT statement every time a field gets added, I was wondering about changing things to the shorter syntax. Will that introduce performance issues? Could this be security related?

Answer 1

There is almost no impact on performance but a big one on readability and extensibility of the code. also you send more data to the sql server because the names takes much more space compared to a single character.

Answer 2

As a general rule in programming, it is almost always a best practice to be explicit.

Select * doesn't really save you that much in terms of maintenance given that the code that consumes that query will have to be updated in most cases anyway.

If you are writing code that just blindly operates on whatever fields happen to be in a specific table, you are asking for trouble. For example, the DBA who adds some kind of maintenance column to a table like a timestamp isn't going to be thinking it will suddenly show up in your app. The best way to future-proof your app is to be explicit.

Also, don't forget about the bandwidth cost in a client-server architecture from dragging down columns of data that you aren't going to use.

I did a more thorough treatment on the performance implications of using select * in this short article I wrote a while back: "Don't Use Select *"