Posted by gosha bine on 08/03/07 14:12

On 03.08.2007 13:06 Michael Fesser wrote:
> .oO(David Gillen)
>
>> If I want all column in a table though surely SELECT * is better.
>
> Depends.

On what, if I may ask?

>
>> In fact, performance on SELECT * is better than "SELECT field1, field2, field3,
>> field4, field5, field6, field7, field8" because mysql doesn't have to go
>> looking for the specific fields you've requested
>
> Did you test that or is it just a wild guess?

I just did and can confirm. SELECT * was (slightly but) better on a
table with 6 cols and 1000 rows.

>
>> but just returns you
>> everything and since you know you want everything there is no problem.
>
> You shouldn't do the work of the optimizer, it knows better how to
> optimize a query in the most efficient way. There are at least two
> problems with SELECT * :
>
> 1) It might return a lot of unnecessary columns, especially when doing
> complicated JOINs.

This is not an issue if we need them all.

>
> 2) The order in which the fields are returned might not always be the
> same. If your application depends on that (there are situations where
> this can be an issue), then it might fail at some time.

If your application depends on that, it shouldn't. Refactor.

>
> Don't you think there's a reason why the MySQL team itself warns that
> you should _never_ rely on using SELECT *, except maybe for testing or
> debugging purposes?

Because they care about how fast their server is, not about how
extensible our applications are, I guess.


--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi

[Back to original message]