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

This question already has answers here: Why is SELECT * considered harmful? (15 answers) Closed 9 years ago.

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_开发者_如何学Goname,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

---

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.

---

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.

---

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...