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

This question already has answers here: Closed 11 years ago.

The community reviewed whether to reopen this question last month and left it closed:

Original close reason(s) were not resolved

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       |                开发者_JAVA技巧| 
| 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.

---

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.

---

If you return fewer columns there is less data to go across the network and less data for the database to process and it will almost always return faster. Databases also tend to be slower using select * because the database then has to go figure out what the columns are and thus do more work than when you specify. Further select * will often return bad results if the structure changes significantly. It may end up showing the user fields you don;t want them to see or if someone is silly enough to rearrange the columns, then the application may actually appear to show things in the wrong order or if doing an insert from the data, put them in the wrong column. It is almost alawys a poor practice to use selct * in production code.