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

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.