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@garfieldtech.com