php - mySQL UPDATE TEXT vs INT field types (speed performance) -

php - mySQL UPDATE TEXT vs INT field types (speed performance) -

i need update undetermined number (max: 32 nos) of random int/float numbers in mysql table.

in php have got them in array:

$array['numbers'][1]=5.5; $array['numbers'][2]=2; $array['numbers'][3]=43; (...)

mysql scenario 1:

+----+-----------------------------------+ | id | numbers(text) | +----+-----------------------------------+ | 1 | a:3:{i:1;d:5.5;i:2;i:2;i:3;i:43;} | +----+-----------------------------------+

mysql scenario 2:

+----+------------+------------+------------+--- | id | no1(int) | no2(int) | no3(int) | +----+------------+------------+------------+--- | 1 | 5.5 | 2 | 43 | +----+------------+------------+------------+---

to update these fields in both scenarios know id (which int auto_increment) include id=$known_id

does create difference in terms of update speed, if serialize these , update scenario 1 ? or whether scenario 2 faster bear in mind 12, 13, 14, or more fields ?

note: separate these spaces , utilize implode() / explode() question remains same. also, focused in update means not looking speed performance select, etc.

edit:

the database same host server , expect update 5 50 rows every 10 seconds or so. also, let's cut down number of random int numbers 12, can prepare serialized string between 512 1024 chars.

it won't create important difference in performance whether store serialized array or multiple columns, unless have high traffic , long strings.

the cases create measurable difference when array long serialized string doesn't fit on single database page. innodb automatically finds page(s) store remainder of string, means more page loads, more disk seeks, etc. avoid overflow pages, serialized string have 768 bytes or less (see blob storage in innodb total explanation of this).

another consideration when have long string , need post whole string update single fellow member of array. bytes have travel across network somehow, , there's no way post substring when utilize serialized-array approach. longer strings, more overhead there doing update. if have lot of these updates executing concurrently, utilize network bandwidth.

for example, gigabite network (1000mbit/s) has throughput of 112 mb/s. if strings average 100kb, , have 1125 concurrent updates per second, that's of bandwidth, on fast, dedicated network. doesn't count other traffic on same network.

re comment , update:

sounds number of updates , average string length quite modest. not great plenty bottleneck. if connect using loopback tcp/ip interface (127.0.0.1) don't have worry bandwidth.

php mysql