sql - Advantages to Vertical Partitioning of Table -

(note situation isn't how is, made example)

i have entity in table data updated every 5 seconds (kinematic data: speed, heading, lat, long, , positiontime), , other data updated hardly @ all, if ever (color, make, origintime).

alt text http://www.freeimagehosting.net/uploads/a67205e99e.jpg

now boss wants me partition data separate tables in our database (with 1 one relationship), so:

alt text http://www.freeimagehosting.net/uploads/1c699bc3c5.jpg

he makes sound "obvious" should way, there advantages having data separated inserting , updating (for instance if put index on color or make)?

it might make sense vertical partitioning this. or might not.

when use mvcc based engine, each time update row, generally* copies entire row , creates new 1 modifications. other transactions not yet see update can continue read original row if need to.

this means updating few small columns in wide row causes database lot more writes needs to.

but not many, because engine sync transaction log, same size regardless of size of non-updated columns, , because data rows stored in blocks whole block needs written anyway, regardless of how of changed.

so sounds potentially pointless optimisation, other, should considered on grounds of a) there performance problem (i.e. optimisation needed) , b) particular optimisation best way of fixing it?

i think chance of a) unlikely, , b) unlikely, chances of being required approximately unlikely-squared.

* engines make exception large columns such big blobs or text columns, held elsewhere , not copied if other columns in row updated.