List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 2 1999 7:51am
Subject:Re: store array of integers using a BLOB?
View as plain text  
On Mo, 1999-08-02 01:38:38 -0500, Jay Jarvinen <3pound@stripped> wrote:
> In effect, I want to store a huge array of binary-coded integers in a BLOB
> type field. So I could store 0 .. 4294967295 in 4 bytes (plus mysql L+4
> bytes for the BLOB column), and avoid storing useless commas.

Storing these integers into a BLOB field to me only would makes sense,
if you want to concatenate them all into one huge binary string which
you then store a a single column.
For example, if these numbers constitute the coefficients of a matrix
and you don't need to access the seperate coefficients, but only need
them as part of the bigger concept "matrix".
Effectively this is the same as storing a GIF image where you don't
need to access the seperate image pixels but only the image as a whole.
Then some 20 adminstrative bytes become totally negligible.

But, IF you need access to each integer (for selection of ranges or
other types of searches), why don't you use a column type of INT?
This is exactly 4 bytes each number (+2 additional for administration
I guess).

Then you just have to convert those binary-coded integers into
their normal ASCII form for insertion (though they are stored
binary internally), and maybe reconvert them back into binary
encoding while retrieving them from the DB again.

> [No, using seperate rows with an INT column isn't an option as it
> would result in millions of rows.]

Why should that be a problem?

> Yet, upon inserting (see below) MySQL seems to magically expand it
> from 4 bytes to 16 (+4 per L+4 in a BLOB column). I'm lost. If
> 'abcd' is 4 ascii char's times 2 bytes, that's 8 ..  plus 4 = 12,
> right? Why 20? I'm lost.

I'm sure Monty or somebody else will give you a detailed explanation
of the ISAM storage format ...

But think about it:
If you're going to insert every number as a BLOB row (which would
 result in 20 bytes each), you're going to have millions of rows again
 (which you seemingly want to avoid).  And could as well use a
 column-type of INT instead.
But if you're not going to store each number seperately and stuff
 everthing into one row, then the few extra bytes don't count, do

Regarding your post scriptum:
> p.s. Due to my limited C knowledge, I was unable to decipher a possible
> answer from the list:

Here the author only talks about the right quoting of binary data for
INSERTs which is done in Perl/DBI by the means of the quote method.

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
store array of integers using a BLOB?Jay J2 Aug
  • Re: store array of integers using a BLOB?Martin Ramsch2 Aug
  • Re: store array of integers using a BLOB? [success]Jay J2 Aug
  • Re: store array of integers using a BLOB? .. correctionJay J2 Aug
  • store array of integers using a BLOB?Michael Widenius3 Aug