What's the beef with * - HTML Forums

Jim7283 · 07-22-2008, 04:11 PM

It seems that time and time again I am finding comments/posts/articles etc. that state how it is bad practice to use SELECT * - why is this the case? What if my database has 10 columns, and I need to use information in every single column in my script? It would seem that using SELECT * would make much more sense that SELECT column1, column2, column3, etc.

Can anyone explain this?

Horus_Kol · 07-22-2008, 07:17 PM

if you do want the full table, then I don't see the problem...

could you share links, so that I can see what these articles are saying?

erisco · 07-23-2008, 12:39 AM

It is bad practice due to maintainability issues. Someone reading the code has absolutely no idea what columns are being selected without looking at the database. Speaking of the database, if that changes, code could break due to extra, missing, or modified columns. Order can also count, and without specifying the order of the columns your assumption could break with database changes.

By declaring what columns you are selecting you assert your assumptions and make them clear. If a problem does exist, its easy to get back an error message from the database, rather than trying to track down where a wrong assumption was made later in your code.

Horus_Kol · 07-23-2008, 01:14 AM

Quote:

Originally Posted by erisco

It is bad practice due to maintainability issues. Someone reading the code has absolutely no idea what columns are being selected without looking at the database.

fair point - i'll give you that

Quote:

Originally Posted by erisco

Speaking of the database, if that changes, code could break due to extra, missing, or modified columns.

Code can be broken regardless of changes to the database no matter how you use your query if your dataobject is not correct

Quote:

Originally Posted by erisco

Order can also count, and without specifying the order of the columns your assumption could break with database changes.

order shouldn't come into it - at least with MySQL, where there is mysqli_fetch_assoc()

Quote:

Originally Posted by erisco

If a problem does exist, its easy to get back an error message from the database, rather than trying to track down where a wrong assumption was made later in your code.

neither way is any easier/harder in this case...

Jim7283 · 07-23-2008, 01:53 PM

Erisco - I'll buy your first point regarding maintainability/reference - it definitely helps to see which columns are being called upon when modifying a script. I'm not sure about the validity your other points, but that one is enough to make me change my practices.

Glad to hear an explanation as opposed to just 'using * is bad practice' which seems to pop-up all over the place...

putts · 07-24-2008, 09:59 AM

When you do a * then the database has to hit the schema to determine the column list that it needs to pull. Same thing goes for using nulls in a database as opposed to ''. If a column is set to allow nulls then every insert/update to the column requires certain checks against the database that add to general overhead.

SELECT *, imo, should never be used in a web application unless your code that is handling it is designed to list all values from the table in some very basic grid. The reason I can say that is (at least in ASP, though I would imagine the same concept applies to PHP) there is also overhead involved in making a reference to a recordset ($result) column using the name. This is because the data object in your code now has to use it's advanced properties to determine where that column is. For optimal performance, all calls to a column that is in some recordset should use the column's ordinal, not its name.

Also, if you're not using all the columns returned, then there's more unnecessary overhead.

Or, simply, the counter-argument - Why do you want to use *? Laziness and whitespace?

erisco · 07-24-2008, 12:31 PM

Horus_Kol, if the database is changed most corruptions will be spotted by an error in the query. If a column name changes your query will fail and you can get the error message back as to what went wrong. If you use * this is impossible. The same is with added or removed columns. It is much easier to see this in a specific error message back from the database than it is to see in an arbitrary error later on in your code.

It is also a weak assumption that order doesn't matter, because any given application may require it where the keys are not known. Sure, order doesn't matter for the majority of cases, but you cannot rule it out completely.

Vege · 07-24-2008, 04:06 PM

Usually sql injections are used with select * querys to select more than orginal writer wanted. When using * the attacker don't have to know table column names as the * print's everything tha's beeing fetched.

Horus_Kol · 07-24-2008, 06:57 PM

Quote:

Originally Posted by putts

When you do a * then the database has to hit the schema to determine the column list that it needs to pull. Same thing goes for using nulls in a database as opposed to ''. If a column is set to allow nulls then every insert/update to the column requires certain checks against the database that add to general overhead.

interesting... now that is a stronger argument than anything else I've read so far in this thread.

However, I do think that PHP/MySQL(i) works with the results from a query differently to ASP/MS SQL...
still - I can see that there will still be some overhead in the database itself.

Quote:

Originally Posted by erisco

Horus_Kol, if the database is changed most corruptions will be spotted by an error in the query. If a column name changes your query will fail and you can get the error message back as to what went wrong. If you use * this is impossible. The same is with added or removed columns. It is much easier to see this in a specific error message back from the database than it is to see in an arbitrary error later on in your code.

That's all well and good, but if your application is expecting some data from your dataobject to be named 'xyz', then it doesn't really matter that you got the column name right in the query itself, you're still gonna get an error if you remove that particular field from your table - just not at the query.
so I don't really see the strength of this statement, I'm afraid - but hey, maybe its just down to the way I code, and it works for me.

Quote:

Originally Posted by erisco

It is also a weak assumption that order doesn't matter, because any given application may require it where the keys are not known. Sure, order doesn't matter for the majority of cases, but you cannot rule it out completely.

Did I say doesn't? Meant to say shouldn't...

putts · 07-25-2008, 07:56 AM

Quote:

Originally Posted by Horus_Kol

However, I do think that PHP/MySQL(i) works with the results from a query differently to ASP/MS SQL...
still - I can see that there will still be some overhead in the database itself.

All of that overhead is done at the database level. The page I had read that at a while back was an ASP website but it never really focused on any one particular database system so I think that is sorta an across the board type situation.

Makes sense, really. If you don't tell the query what fields you're using, the database needs to figure out what values to return somehow.