Select * sql query vs Select specific sql query columns

Question

Possible duplicate:
Why is SELECT * considered harmful?

There is probably a problem with the nOOb database.

Our application has the following table

TABLE WF

Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | children | text | YES | | NULL | | | w_id | int(11) | YES | | NULL | | | f_id | int(11) | YES | | NULL | | | filterable | tinyint(1) | YES | | 1 | | | created_at | datetime | YES | | NULL | | | updated_at | datetime | YES | | NULL | | | status | smallint(6) | YES | | 1 | | | visible | tinyint(1) | YES | | 1 | | | weight | int(11) | YES | | NULL | | | root | tinyint(1) | YES | | 0 | | | mfr | tinyint(1) | YES | | 0 | | +--------------------+-------------+------+-----+---------+----------------+ 

This table is expected to contain more than ten million records. The pattern is not expected to change. I need to get the columns f_id, children, status, visible, weight, root, mfr.

Which approach is faster for finding data?

1) Select * from WF where w_id = 1 AND status = 1;

I will remove unnecessary columns at the application level.

2) Select children,f_id,status,visible,weight,root,mfr from WF where w_id = 1 AND status = 1;

There is no need to separate unnecessary columns as preselected in the query.

Does anyone have a real life benchmark that is faster. I know some say that Select * is evil, but will MySQL respond faster by trying to get the whole fragment instead of retrieving selective columns?

I am using MySQL version: 5.1.37-1ubuntu5 (Ubuntu), and the application is a Rails3 application.

Answer 1

As an example of how a select statement, which includes a subset of columns, can be significantly faster, it can use a coverage index in a table that includes only these columns, potentially leading to significantly better query performance.

Answer 2

If you return fewer columns, there is less data and less data to process the database to transfer data over the network, and it will almost always return faster. Databases are generally slower using select *, because the database then needs to figure out what the columns are, and thereby do more work than when specifying. Further choices * will often return poor results if the structure changes significantly. This can lead to the appearance of custom fields that you don’t want, or want someone to be dumb enough to reorder the columns, then the application may actually appear in the wrong order or insert from the data, put them in the wrong column. It is almost reckless to use selct * in production code.