Should I select just the columns I require in MySQL statements?

Or can I just select *?

[groovyhippo]

In general, when I do a select query on my MySQL database, I tend to do "SELECT * FROM" rather than selecting just the columns I require.

Does this have much of an impact on performance?

[jpjones]

In general, specify the columns you require.
If you do a SELECT *, then this returns all the data for each row. Consider this: if your table has 10 columns, and you need the information in 5 columns. That's 5 redundant columns of information that is transferred from your database server to your application, eating up memory in the transfer. That's wastage :(

If you specify the columns you require, then you've got a more optmised application.

My thoughts....
JP

[txbakers]

Also, if you say "Select *" the database first has to run a SQL command to get the names of the columns, causing a double hit on the server. Even if you want all, you should type the names of the columns in your query.

Then, to avoid endless typing, you can refer to the columns by their position number in the query:

Select name, password from users order by 1

rsNames(1)

etc.

[Gibble]

ACK, don't use position numbers! If you happen to change your query, you then have to go through double checking numbers, etc Use the column name, it's much safer

[jpjones]

ACK, don't use position numbers! If you happen to change your query, you then have to go through double checking numbers, etc Use the column name, it's much safer

Its also slower! By referring to column names, then there is an extra call where the corresponding database column number is checked for, before it retrieves the information from the correct column. If you're trying to build an application for speed, to handle the largest possible number of users, refer to the columns by number.

If you change your query, you only have to go through the script once making the necessary corrections.

JP

[groovyhippo]

Thanks everyone, you've convinced me to start naming those columns now!

[ukgimp]

A must read:
[webmasterworld.com...]