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

[Gordon Heydon]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

No actually in e-Commerce I have set standards where we do this where  
ever we can. this means that with very little code an external module  
can extend a table.

Give that we use drupal_write_record() to write all records someone  
can use the hook_schema_alter() and the hook_form_alter() to add  
columns to a table.

Take the ec_product table, we do a SELECT * from {ec_product} WHERE  
vid = %d. Then using the hook_form_alter() to add additional fields to  
the form to capture data. Other than validating that the field is  
correct when e-Commerce calls drupal_write_record() then entire node  
is past, which means no additional processing needs to be done, and  
the new custom fields will get saved as required.

But you can have problems like what you describe is there number of  
columns is a large number. So if you are joining 20 odd tables with a  
SELECT * to return all fields can cause problems.

It is really up to the developer to make sure they implement it  
correctly.
Gordon.

On 13/02/2009, at 4:08 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.
>
>
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)

iEYEARECAAYFAkmVA3cACgkQngavurZvkryJJwCgip4b17BquRwlmoLUeMX3ZrKK
yGkAmQGe4YusD6sU9umm0E1AEJvdgu5M
=RKUH
-----END PGP SIGNATURE-----