Field Order and Performance

[DoubleDee]

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

[r937]

makes no difference

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

[DoubleDee]

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

[r937]

Anyways, I think that the Left-to-Right Order of your columns does affect query speeds...

in that case, don't move it

[DoubleDee]

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

[r937]

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

[DoubleDee]

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

[r937]

I don't understand what you mean?

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

[DaveMaxwell]

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.