Using * in SELECT Query [duplicate]

Question

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

I am currently porting an application written in MySQL3 and PHP4 to MySQL5 and PHP5.

On analysis I found several SQL queries which uses "select * from tablename" even if only one column(field) is processed in PHP. The table has almost 60 columns and it has a primary key. In most cases, the only column used is id which is the primary key.

Will there be any performance boost if I use queries in which the column names are explicitly mentioned instead of * ? (In this application there is only one method which we need all the columns and all other methods return only a subset of the columns)

Answer 1

It is generally considered good practise to only fetch what is needed. Especially if the database server is not on the same machine, fetching an entire row will result in slower queries, because there is more data to transport over the network to the consuming machine. So if a full row is like 100k of data and you only need the ID which is much less, you will get faster results of course.

As a general tip for optimizing queries, use the EXPLAIN statement to see how costly a query will be.

Answer 2

"Premature optimization is root of the all evil". Donald Knuth.

Never ask a question like Will there be any performance boost?. But ask only a question like "I have certain bottleneck. How can I eliminate it?"

In 99% of our applications, this "improvement" would be irrlelvant. As many other improvements, based on the dreams, not on the profiling and real needs.