MySQL and PHP array storing question

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

More information might be helpful:

• Where are you getting this data from?
• Is it actually numeric, or is that just simplification for your example? If numeric, what range/accuracy is required? Is it discrete data, ie from a limited selection of recurring values? Does the data have to be exact, or would a close approximation still be good enough?
• What does it mean? How are you using it?
• Are you most interested in speed, or storage space, or code cleanliness, or what?
• How often does it change?
• Is that 700Mb all one array? Are your PHP instances memory-limited? Have you looked at how much memory your script uses, and how that varies with different array sizes?
• What is the largest single array you anticipate ever seeing (in terms of number of items / storage space)?
• How many different data arrays might you have to deal with?
• Is there any "overlap" between data sets, ie recurring data?

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.