well its ALLWAYS good practice to only select the fields you want to process, and this will reduce the memory usage of the database.

A lot of mysql/php devs have learned a lot of very bad habits unfortunetly - I was lucky and got taught the ORACLE way and had a DBA one of whose claims to fame was "oh Dijkstra was my first boss"

and dont relay on the ordial position of results - always refer by name.

I notice in most database abstraction classes for PHP, most select methods simply assume that you wish to select all fields from a row

For showing examples, it's less confusing to keep things simple. The more details you show, the more likely people will miss the important points.

In practice, it's generally better to just select the fields you need. Especially if you are returning a lot of rows.

Think of it this way, all the fields that you don't need have to be stored into memory. If your application server is on a different box from your database server, that unneeded data also has to be passed across the network.

Thanks.

well its ALLWAYS good practice to only select the fields you want to process, and this will reduce the memory usage of the database.

I wish it was ALWAYS this easy! The actual answer would greatly depend on the DB and what exactly you do with the data after you've selected it. For example, in MySQL (at least in the MyISAM DB) you have to be really careful about which fields you drop from selecting because if you accidentally removed a field that you don't need for further data processing but use for sorting, you'll force mysql to open the same table twice: first open a temporary table and fill it with the actual fields you said you wanted, then open THE ENTIRE table again, select the omitted field and sort the first table based on results of the second select. This can throw a monkey wrench into your memory calculations when you set your server up: you may easily run out of available memory for those temporary tables storage and start saving stuff to disk which is REALLY slow.
This is what I'm talking about:

SELECT * FROM `posts` ORDER BY `date` DESC

will execute faster (or much faster if you throw a WHERE condition in there) than

SELECT `subject`,`body`,`author` FROM `posts` ORDER BY `date` DESC

because in the latter example I forgot to select `date` on the first pass and mysql will have to do the second. Even though I did not need the `date` for later processing, I have to not forget to include it into the SELECT if I do something with it in the same SQL statement.

So, yeah, as a general rule "Select only what you need" is true but you have to have an understanding of what exactly is it that you need - sometimes you need more than what's obvious.

Some other things to consider.

It isn't just the DB that you will affect by returning all fields.

calls to things like mysql_fetch_array will be more costly on the processor as well it also affects the DB_Conn in many cases.

1script is right you do have to make sure that you know which fields you need and it isn't just the ones you are displaying on page.

1script- very interesting point! Any idea if other DBs have the same behavior?

@LifeinAsia: if you mean other types of DBs supported by MySQL then the answer is *most likely* yes. I'm just basing my examples on the real optimization work I do these days and the DBs are mostly MyISAM. In any case, I would advise anyone interested to go straight to the source: [dev.mysql.com...] While there, check out other optimization techniques and examples they link to in the right panel.

Actually, I technically meant other DBMSes like MS SQL, Oracle, etc.