Field Order and Performance

Databases
1

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

makes no difference

why in heaven’s name would it even occur to you to move it?

3

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?!) :cool:

Debbie

4

in that case, don’t move it :smiley:

5

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
  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

7

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

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

9

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.