Select * sql query vs Select specific columns sql query [duplicate]

Question

Possible Duplicate:
Why is SELECT * considered harmful?

Probably a database nOOb question.

Our application has a table like the following

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 be upwards of ten million records. The schema is not expected to change much. I need to retrieve the columns f_id, children, status, visible, weight, root, mfr.

Which approach is faster for data retrieval?

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

I will strip the unnecessary columns in the application layer.

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

There is no need to strip the unnecessary columns as its pre-selected in the query.

Does any one have a real life benchmark as to which is faster. I know some say Select * is evil, but will MySQL respond faster while trying to get the whole chunk as opposed to retrieving selective columns?

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

Answer 1

As an example of how a select statement that includes a subset of columns can be significantly faster, it can use a covering index on the table that includes just those columns, potentially resulting in much better query performance.