in reply to MySQL - Query vs Perl

I'm probably making some assumptions here, just some random thoughts:

If all the numbers are single digits what is the point of having commas between them?

If all the digits are between 1 and 8 like you have written, they seem like octal! You could store 10 octal digits into an unsigned integer (4 bytes). Better than 1 digit every 1 byte! Really I just want to use octal for more than chmod...

Replies are listed 'Best First'.
Re: stooping lower
by Trihedralguy (Pilgrim) on Aug 31, 2008 at 13:25 UTC
    @juster

    I just really just leaving room for the idea that someday the data might consist of two digit numbers, or perhaps even more.

    @klassa

    The multi-row method is exactly what I need, just stored differently, and if I say archive that everyday, that will only leave me with 365,000 rows by the end of the year, which isn't bad at all.

    Thanks to everyone again for the quick responses!
[reply]
      I just really just leaving room for the idea that someday the data might consist of two digit numbers, or perhaps even more.

      If you think they're going to stay within 255, you can pack them into a string, and then just store it in a char field. (not varchar -- no need for the extra overhead, as you know how many values there are going to be -- if the number of values change, you can then always alter the table

      Even leaving yourself two bytes per stored value is only going to be one character more than a comma-separated string is going to be. (and if you're using a varchar, that extra character overhead is moot)

      ...

      But that being said, there's a few other considerations that probably won't come out without benchmarking trying the single-record-per-user vs. the record-per-value approach --

      • How often are the values being updated?
      • Are the values needed singly, or a whole lot at a time?
      • Are the values updated singly, or many at the same time?

      And as you mention archiving -- you might want to consider that your archiving might not need to match your storage for normal use. Even if you store the data for normal use as a record per value, you can store it in a more compact format for long term archiving.

[reply]

In Section Seekers of Perl Wisdom