Best to use * when calling a lot of fields in mysql? [duplicate]

Question

I know generally it is always better performance to build mysql queries to name every item you need but for example on a profile page I might need every item except a few.

    SELECT user_name,f_name,l_name,country,usa_state,other_state,zip_code,city,gender,birth_date,date_created,date_last_visit, 
user_role,photo_url,user_status,friend_count,comment_count,forum_post_count,referral_count,referral_count_total, 
setting_public_profile,setting_online,profile_purpose,profile_height,profile_body_type,profile_ethnicity, profile_occupation,profile_marital_status,profile_sex_orientation,profile_home_town,profile_religion, 
profile_smoker,profile_drinker,profile_kids,profile_education,profile_income,profile_headline,profile_about_me, 
profile_like_to_meet,profile_interest,profile_music,profile_television,profile_books,profile_heroes,profile_here_for,profile_counter FROM users WHERE user_id=1 AND user_role >

So without doing a bunch of test, maybe someone with more experience can chime in with some advice?

Would this be worse

SELECT * FROM users WHERE user_id=1 AND user_role >

I prefer to list all items because then on that page it is just easiar to see what I have available to me if I need something from the DB but if it would be faster then I would not list them

Furnish

Answer 1

Note: naming all fields is of course a best practice, but in this post I will discuss only performance benefits, not design or maintenance ones.

---

The * syntax can be slower for the following reasons:

• Not all fields are indexed and the query uses full table scan. Probably not your case: it's hardly possible that all fields you return are indexed with a single index.

• Returning trailing fields from a table that contains variable length columns can result in a slight searching overhead: to return 20th field, previous 19 should be examined and offsets calculated.

• Just more data need to be returned (passed over the connection).

Since you need almost all fields, the last reason is probably the most important one. Say, the description TEXT field can be only 1 of 50 fields not used on the page, but can occupy 10 times as much space as all other fields together.

In this case it will be of course better to name all fields and omit the long fields you don't need.

Specular

But what if you need ALL the fields of the table? – Shaughn

Then you should select them all of course. MySQL will internally expand the * into the complete list of the fields, there will be no performance difference. – Specular

@Specular Regarding your first point, I don't quite understand how a select expression, in our case *, can effect index usage. As I understand it, if WHERE condition works with an indexed attribute then corresponding index will be used to find records satisfying the condition. After records have been located RDBMS will load attributes required by the select expression. From this point of view, index usage shouldn't depend on a set of loaded attributes. – Externalism

@Mr.Deathless: if all the fields you select (and filter on too) are contained in the index, the RDBMS won't need to look up them up in the table itself. – Specular

Answer 2

When considering using *, you should always consider the possibility that more fields will be added to the table later.

If it's a lot more fields, you could end up retrieving and returning more data than you need.

You might have a problem with some of the new fields. For example, if you just loop through the fields and display them, you might have new fields you do not want to display. Or the data type might need some formatting first.

There is also a chance that a field will be removed from the table, for example in normalizing the table. Code that expects a particular field could break in that case.

Sardis

Answer 3

You should always specify the columns you need, unless your programming language supports associative lists/arrays, so that names can be retreived by name.

If you need to retreive it by index number, then using * could pose a huge problem later if you insert a new column anywhere in the table, as all the indices from that point will increase by one...

Exodus