Query: use * instead of selecting ALOT of columns = less overhead?

[matsko]

I was reading a mysql optimization webpage, it said that in some cases its smarter to use a less detailed query to do a task.

One example was to use default values when needed. For exmaple, you insert a record into a database that has alot of fields (columns) and when it is inserted, there may be many columns that have default values that the record will contain. So when you make a query that does not include any indication for those columns then the query will be parsed faster...

I agree with this logic, however I was wondering if the same thing applies to SELECT statements.

Ok lets say you have a very long table, which has alot of fields. You need to select all the fields (all but the WHERE clause field). So you select them field by field (field1,field2,field3,field4) and you get what you need. Since there are so many fields, then would it be logical to select them all using the asterix (*)? that way mysql wont have to parse the select statement to the extent that the field by field statement would have to be.

Remember this is only for a field that has ALOT of fields (10+) and ALL fields must be fetched.

so what do you guys think....

[r937]

i believe mysql does have to parse "select star" anyway (it has to pull the column names out of the information schema pages)

but "select star" has other disadvantages, so mostly i recommend never using it

mostly

[matsko]

oohh ok

I see, it still has to read the database information file to get a list of all the fields. But would still have to determine the fields anyway if it was parsed without the *.

well what I am asking here is that, the parsing of the query statement itself (not the fields, since that occurs regardless).

like I said before, I would only use it when I am selecting ALL the fields within a column. For exmaple, a profile table...

id | name | age | country | hair | eyes | height | weight | occupation | date | login

that has 11 fields. However the query here will be SELECT name,age,country,hair,eyes,height,weight,occupation,date,login FROM profiles WHERE id='#' LIMIT 1

this works fine, however I would assume that it would be faster to parse when the asterix is there since pretty much all of the fields are being selected. As you can see you have selected 10 of the 11 fields (since you already know the id value), but with the * option you are also selecting the id value (which is a small number <= 2 bytes).

so... once again, according to this scenerio, would it be more logical to select all of them with the * method or all of them -1 with the column by column method?

[r937]

more logical? not to me

but you seem pretty keen on it, so go ahead

if i'm running ad-hoc queries, sure, i do it all the time, it beats typing out the actual field names, but for an application? permanent code? don't do it

do a search for the phrase "why select star is bad"

[SecondV]

more logical? not to me

but you seem pretty keen on it, so go ahead

if i'm running ad-hoc queries, sure, i do it all the time, it beats typing out the actual field names, but for an application? permanent code? don't do it

do a search for the phrase "why select star is bad"

Save him a little effort, i'm nice like that lol...

why "select star" is bad

[stereofrog]

I think "select star" is a Good Thing, because it decouples your code from database structure. Imagine you're going to add a new column to the profile table above. With "select star" you only need to modify your html template, with no need of changes in sql or php code.

[r937]

stereofrog, you can't be serious

[stereofrog]

Are there any other reasons to avoid star except performance? Your explanation wil be greatly appreciated.

[r937]

i outlined my reasons in the article linked in post #5

[stereofrog]

thank you