SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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....
    I can't believe I ate the whole thing

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    I can't believe I ate the whole thing

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast SecondV's Avatar
    Join Date
    Jan 2006
    Location
    Kentucky
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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"
    Save him a little effort, i'm nice like that lol...

    why "select star" is bad

  6. #6
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    stereofrog, you can't be serious
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are there any other reasons to avoid star except performance? Your explanation wil be greatly appreciated.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i outlined my reasons in the article linked in post #5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •