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

[Larry Garfield]

SELECT * is slower than listing out fields explicitly, because the database 
then needs to take the time to expand * out into the available fields before 
compiling the query.  However, it's not so much slower that its use would kill 
the DB server unless you have a table with hundreds of fields that you're not 
using (or lots and lots of very large BLOB or LOB fields), in which case you 
have a bad database design to start with.  So I don't believe that SELECT * 
would be solely responsible for melting your server.

That said, for both that performance reason and for 
maintainability/readability of the code I generally discourage SELECT *, and 
try to avoid it myself.  The D7 database layer allows it in static queries 
(which are almost entirely un-interpolated) and has a mechanism for supporting 
them in dynamic queries (fields('tablename') with no second parameter), but I 
still avoid it myself and encourage others to do so as well.

So no, not a bug, but I do agree with discouraging it in most cases.  The only 
time you'd want it is if you have a table where you expect the columns to be 
dynamic, which has all sorts of other maintenance issues anyway.

On Thursday 12 February 2009 11:08:25 pm augustin (beginner) wrote:
> Hello,
>
>
> A while ago, someone (not me!) brought down our collocation server with a
> home made script which had many queries like this:
>
>  SELECT * FROM ...
>
> I.e. the script was indiscriminately selecting everything from the table
> even though he might have needed only a few fields.
> This created heavy traffic between the SQL server and the web server which
> overwhelmed the platform. To be honest, I don't know the details (he might
> have been needlessly transferring whole images stored in the DB or
> something like this!)
>
>
> In Drupal 6.9, I count 120 occurrences of SELECT * FROM ... , not including
> SELECT COUNT(*) but including variations of SELECT n.*, pi.* FROM ... .
>
> Wouldn't it be a good idea to explicitly tell which fields are needed, in
> order to minimize traffic between SQL server and web server?
>
> The SQL coding standards say nothing about this:
> http://drupal.org/node/2497
>
> Should SELECT * FROM be considered a kind of bug?
> Should this whole discussion be a 'won't fix' ? (why?)
> Or is this something worth considering in the issue queue?
>
>
>
> Blessings,
>
>
> Augustin.

-- 
Larry Garfield
larry at garfieldtech.com