[development] Is "SELECT * FROM ..." ok?

[Ken Rickard]

One additional (bugworthy) note:

Using SELECT * on a {node} query breaks db_rewrite_sql() in Drupal 5
and Drupal 6. [1]

This opens a quasi-security hole where content may be shown to
non-privileged users.

This is due to the fact that node_db_rewrite_sql() adds 'distinct' ==
TRUE, and you cannot run DISTINCT(*) queries.

So, in the case of node list queries, SELECT * is _definitely_ a bug.

- Ken Rickard
Palantir.net

[1] -- http://drupal.org/node/324070


On Fri, Feb 13, 2009 at 10:23 PM, David Metzler <metzlerd at metzlerd.com> wrote:
> I would definitely agree here.  Selecting from watchdog definitely deserves
> to have the columns listed.  Selecting from taxonomies and categories are
> the same.   But there are places in CCK and views that select * might be
> considered more appropriate.   As a developer I'd love to see such comments
> in a patch review or code review, but I wouldn't love to see a bunch of
> issues filed because someone grepped my code for select *.
>
> I was really pointing out the fallacy of the select * = poor performance
> argument, and that you might do as well to find out how many ROWS were
> returned by the select statement in question, plus start searching for outer
> joins ,etc.
>
> And don't event get me started on insert statements without column lists..
>
> dave
>
>
>
> On Feb 13, 2009, at 7:15 AM, Morbus Iff wrote:
>
>>
>> I give *little* regard for performance (in this case), and everything for
>> my *expectations* and *documentation*. I could care less if it's faster or
>> not. I care more about clarity.
>>
>> David Metzler wrote:
>>>
>>> Although there are merits in this argument, I wouldn't classify it as  a
>>> bug.  There's a lot of generic/abstracted data loading, and as long  as
>>> there's code at there that dynamically adds columns, select *  actually is
>>> the sanest way to do things.
>>> Also, the performance costs are database dependent. Mysql may work  one
>>> way, but other db's work another.   Any DB with precompiled  cached queries
>>> is not going to carry significant parsing overhead.   The amount of data
>>> transfered in most cases is dependent on the size  of the data in the column
>>> and not the number of columns.  So in some  tables it may make sense.  You
>>> might get the name of the image file  in drupal, but you're not likely to
>>> get the image.
>>> Finally in my experience, most database performance problems lie in  what
>>> is in the WHERE or JOIN, and not what's in the column list.
>>
>> --
>> Morbus Iff ( masochism-oriented recombinant bot (unlisted series) )
>> Technical: http://www.oreillynet.com/pub/au/779
>> Enjoy: http://www.disobey.com/ and http://www.videounderbelly.com/
>> aim: akaMorbus / skype: morbusiff / icq: 2927491 / jabber.org: morbus
>
>



-- 
Ken Rickard
agentrickard at gmail.com
http://ken.therickards.com