Using * in SELECT Query

Question

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

Will there be any performance boost if I use queries in which the column names are explicitly mentioned instead of * ? - YES

Answer 2

Yes. If you're fetching more data than you need then that has to be read from disk, sent between MySQL and PHP, etc. which is probably going to take longer.

Also I think I read somewhere once that SELECT * is bad because the database first needs to look up the columns it is going to fetch before doing the query, but I may be wrong on that.

Answer 3

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 4

If and how much you benefit depends on the case, but at least for the cases when you only need the id column, you should fix the SQL.

In addition to the reduced network traffic (of sending useless data), the database may be able to get to the few columns you do need just using indexes, without accessing the table at all. That would speed things up a lot.

The only possible downside is the increased number of distinct SQL statements that the server has to process (and more complex code on your end).

Answer 5

No - there will be an impact on performance but as long as there aren't BLOBs/CLOBs in the schema it will be negligible (unless you access your database over a 300 baud modem) - most of the work done by the database is in identifying the rows matching the WHERE clause - however its (IMHO) bad programming practice to use SELECT *

C.

Answer 6

"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.

Answer 7

Yes. Fetch only the columns you require. Not only can this improve performance, but it will prevent your code from inadvertently breaking. Consider this query:

SELECT * FROM tabA JOIN tabB on ... ORDER BY colX

They query works today when only tabA has colX, but if you change schema and add colX to tabB, the query will abend.

Of course using table aliases for all fields will also help prevent breakage.

-Krip