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.

You're the one with the 10 million record table; how much more real life can you get than your benchmark? — JeffO
– JeffO, Commented Feb 16, 2011 at 18:31
I am doing a migration onto Rails. I dont have access to the current production. Technically, I do not have the data right now. — paddle42380
– paddle42380, Commented Feb 16, 2011 at 18:32
@Jeff O I wish you got points for comment upvotes. That comment says it all. — corsiKa
– corsiKa, Commented Feb 16, 2011 at 18:32
@p.campbell et al: Trigger happy closers - the "duplicate" question is too general, this one has a specific scenario with specific structure and volumes as well as a specific RDBMS (MySQL). Not to mention app framework (Ruby) as well. A very detailed specific answer could be arrived at for this question — RichardTheKiwi
– RichardTheKiwi, Commented Feb 16, 2011 at 18:48
@p.c I disagree. The question was not about maintainability, robustness, standards, (anti)patterns - it was simply put Which approach is faster for data retrieval? There are other questions around (C arena) about the fastest way to do something, for which the answer could be ASM (not pretty C) which would be the right answer. The linked "duplicate" is discussing broad-brush theory and best practice. — RichardTheKiwi
– RichardTheKiwi, Commented Feb 16, 2011 at 19:26

Michael Goldshteyn · Accepted Answer · 2011-02-16 18:49:15Z

5

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.

edited Feb 16, 2011 at 18:49

answered Feb 16, 2011 at 18:34

Michael Goldshteyn

74.9k25 gold badges136 silver badges183 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nathan DeWitt Over a year ago

Upvote for mentioning covering index. I came here to say that.

paddle42380 Over a year ago

Thanks, got the jist about the covering index.

RichardTheKiwi Over a year ago

@papdel A covering index for this query will take 8 (of 12) columns, and will be terribly expensive (relative measure) to maintain

paddle42380 Over a year ago

Thanks Richard. I got too psyched with all the sudden backlash!

Michael Goldshteyn Over a year ago

@Richard, that depends on the ratio of inserts to queries. Even an 8 of 12 covering index could be worthwhile if the number of queries far outnumbers the number of inserts. Also, the 8 of 12 columns was an example, I gave a general answer to the general question.

|

HLGEM · Accepted Answer · 2011-02-16 18:34:06Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related