user1111929 user1111929 - 1 year ago 53
SQL Question

smallest storage of integer array in mysql?

I have a table of user entries, and for every entry I have an array of (2-byte) integers to store (15-25, sporadically even more). The array elements will be written and read all at the same time, it is never needed to update or to access them individually. Their order matters. It makes sense to think of this as an array object.

I have many millions of these user entries and want to store this with the minimum possible amount of disk space. I'm however struggling with MySQL's lack of


I've been considering the following options.

  • Do it the MySQL way. Make a table
    with columns
    . To make this efficient, one needs an index on
    , totalling well over 10 bytes per integer.

  • Store the array in text format. This takes ~6.5 bytes per integer.

  • making 35-40 columns ("enough") and having -32768 be 'empty' (since this value cannot occur in my data). This takes 3.5-4 bytes per integer, but is somewhat ugly (as I have to impose a strict limit on the number of elements in the array).

Is there a better way to do this in MySQL? I know MySQL has an efficient
type, so ideally I'd store my 2-byte integers as 2-byte chars in a
(or a similar approach with
), but I'm not sure how to do that. Is this possible? How should this be done?

Answer Source

The best answer was given in the comments, so I'll repost it here with some use-ready code, for further reference.

MySQL has a varbinary type that works really well for this: you can simply use PHP's pack/unpack functions to convert them to and from binary form, and store that binary form in the database using varbinary. Example code for the conversion is below.

function pack24bit($n) { //input: 24-bit integer, output: binary string of length 3 bytes
    $b3 = $n%256;
    $b2 = $n/256;
    $b1 = $b2/256;
    $b2 = $b2%256;
    return pack('CCC',$b1,$b2,$b3);

function unpack24bit($packed) { //input: binary string of 3 bytes long, output: 24-bit int
    $arr = unpack('C3b',$packed);
    return 256*(256*$arr['b1']+$arr['b2'])+$arr['b3'];