I have an array with 1440 vars in it. something like:
$array = array(1 => 45.76, 2 => 67.56, 3 = 79.23 ..... 1440 => 20.22);
This array is serialized and inserted into MySQL table with longtext as field type.
What I'm facing now is a table with 700MB of data and that will mean some serious problems in the near future.
Is there a better way of storing this array, I've heard of php pack() function, but how will it help me? What about mysql BLOB type filed instead of longtext?
Thank you
Don't store arrays this way. The array syntax will give huge overhead.
It's better to use a row for every entry, like so:
TABLE `array_entries` (
`Index` INT UNSIGNED NOT NULL,
`Value` DOUBLE,
`Name` CHAR(8) COMMENT 'name of the array the entry belongs to',
PRIMARY KEY (`Name`, `Index`)
);
will allow you to store multiple arrays in one table, and only load what you need (or a whole array if you like).
Of course, you can replace Name by a (integer) foreign key pointing to a table arrays containing more info about the arrays.
arras table, and an index on that column, and an index on whichever column is sorted (probably index). What is the name column for?
More information might be helpful:
Taking your example as representative, I would consider multiplying each number by 100 and casting to integer then storing as raw binary. Your array becomes 2880 bytes of data. If your data rarely or never changes, you could skip-read this directly from a binary file very efficiently.
Take a look at gzdeflate for compressing strings. Example from the doc:
<?php
$compressed = gzdeflate('Compress me', 9);
echo $compressed;
?>
so in your case, something like this might do:
<?php
$compressed = gzdeflate(serialize($array), 9); //or var_export?
echo $compressed;
?>
On modern hardware, 700MB of data in a MySQL table doesn't look so bad. Of course, it's always better to minimize the tables size, so you could use one of the compression functions that PHP provides. Read on here for more informations.
I often use gzdeflate, because it's quite fast and compresses text pretty well. But plenty others are available.
