* VS multiple fields

LordRogaine

I'm curious to know if there is a difference or if one way is faster than another. If I am using a select statement and I want to call up all the fields from a large table, is it any faster or more efficiect if I write my select statement like this--

select * from table_blue;

select field1, field2, field3, field4, field5, field6 from table_blue;

Naturally the first way is easier to write out. But is one way anymore efficient than the other if I am using a very large table with a lot of information?

Thanks in advance,
Lord Rogaine

Kudose

It is more efficient to specify which columns you want if you plan on returning multiple rows. It shouldn't matter much with a single record.

bogu

Writing the field name when u create a SELECT query; don't know if the question for this is if the select is faster or not...

Lets put it this way:

If u make a selection and select all the field but u need only a few of them take the select result into an array, if u put all the fields in that array and not only the one that u need the array will be much bigger then u need (more memory to use)...

Another example will be, when u join two tables that have fields with the same name, u need to write those fields name to create aliases ...

And much more examples; so for me is best to write the exact name of the fields I want if I don't need all the field and use "*"(all the fields) when I don't join tables and I need all of them ...

MarkR

Although conventional perceived wisdom was "Select * is bad", I'm tending to switch my own opinion:

"Select * is usually ok".

There are times when "Select " is a very bad idea, but they don't happen that often. With a small amount of denormalisation of your database for performance reasons (e.g. putting big blobs in a separate table), then there is almost no case where "SELECT " won't be ok.

The big advantage of "SELECT *" is that it allows you to more usefully reuse code, than if you only select columns you need.

Finally, if you're often going to be selecting some common subset of columns from the same table / tables (perhaps with a join), creating a view might be a good idea, and will make your code simpler and more consistent.

But don't go overboard on this - specifically, views of views make an application confusing to maintain.

Mark

LordRogaine

thanks everyone!