SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Field Order and Performance

    Does the order of Fields affect the performance of a Table?

    For example, if my table has...
    - id
    - email
    - hash
    - temp_password
    - temp_reset_on
    - first_name
    - last_name
    - reg_date
    - reg_ip
    - activation_code
    - created_on
    - updated_on
    ...and I moved "email" - which is a commonly used and important field - to the end like this...
    - id
    - hash
    - temp_password
    - temp_reset_on
    - first_name
    - last_name
    - reg_date
    - reg_ip
    - activation_code
    - created_on
    - updated_on
    - email
    ...then how would that affect the performance of my Table and Queries?


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    makes no difference

    why in heaven's name would it even occur to you to move it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    makes no difference
    Are you certain about that?? (I remember learning in my Oracle classes that it *does* make a difference...)


    why in heaven's name would it even occur to you to move it?
    I created my table with the fields in "Order of Importance".

    Now I think it would make them easier to work with if they - or some - were in "Order of Process Flow".

    For example, you have an "activation_code" before you would have a "temp_password".

    Likewise, you need a "salt" before you can create a "hash".

    It is a minor point, but detail matters!!!

    Anyways, I think that the Left-to-Right Order of your columns does affect query speeds... (I think it is how the database stores the data and accesses it in "memory blocks", but who knows?!)


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Anyways, I think that the Left-to-Right Order of your columns does affect query speeds...
    in that case, don't move it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in that case, don't move it
    Seriously, is that correct or not?

    You seem to thing it doesn't matter, but I recall in Oracle it could make a difference if you had a really big table...


    Debbie

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    1. the table you showed isn't a really big table
    2. this ain't oracle

    stop obsessing over the tiniest details, deb

    if you're worried about performance, test it for yourself -- do before and after EXPLAINs
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1. the table you showed isn't a really big table
    2. this ain't oracle
    I know, but I figured it is better to ask now versus later.


    stop obsessing over the tiniest details, deb
    Why, do I "obsess"?


    if you're worried about performance, test it for yourself -- do before and after EXPLAINs
    I don't understand what you mean?

    How would I do those fancy queries that say "Executed 10,000 records in 0.15 seconds" or whatever?!


    Debbie

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I don't understand what you mean?
    http://dev.mysql.com/doc/refman/5.0/...g-explain.html
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Take it for what it's worth, but it does look like there is/was a slight performance improvement - in Oracle only (from what I can tell) on UPDATES (and really, the only benefit was in rolling back a large number of updates), but the only result I can find on a search are from 2008, so I wouldn't be able to verify whether the claims are accurate or not anymore. And even then, the performance difference was minimal and it only made a difference on large (100K+ row) updates.

    The only other place where I've seen an order be affected was on a query dealing with memo/text fields, but that was classic asp and ADO, and TBH, I don't believe that problem even exists anymore. It had nothing to do with the table or the performance, but rather how the ADO handled the values within the recordset returned.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


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
  •