|
|
Posted by David Gillen on 08/03/07 14:08
Michael Fesser said:
> .oO(David Gillen)
>
>>If I want all column in a table though surely SELECT * is better.
>
> Depends.
>
>>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 did timing tests on it quite a while back and it was the case.
This may have changed with later versions of mysql.
>>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.
I'm not doing complicated joins I'm selecting from a single table, two at most
and I want ALL fields, expect the field the join is on if I'm joining two
tables.
> 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.
>
The order of fields returned is not important to me, if it was I would see it
as a problem with my code. If I do a select * followed by mysql_fetch_object
or mysql_fetch_assoc I'll know exactly where each column from the table is.
> 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?
>
I wasn't aware that they were saying that so it is news to me, I'll go and
read more on this now.
But I have yet to see a valid reason for it other than the possibility that if
your application depends on the order of the returned fields it may fail at
some point. Something which is irrelevant for me because of the way I write my
code. If you are writing code which is dependant on the order then you should
revise your code. Simple as.
D.
--
Fermat was right.
Navigation:
[Reply to this message]
|