3

We want to store a serialized object in a table along with a unique internal ID. We'd only ever want to read/write/(rarely)update the rows. We'd never interact with the serialized field just the ID. We're using InnoDB,

Firstly;

Would it be right to store the serialization as a text type field?

Secondly;

If we do not directly interact with the serialized field other than r/w will it affect the performance of our database at all?

Finally;

Would it be better to store the serialized object in our filesystem instead?

To give a little insight into why we're storing them in the first place, we receive an object from a supplier, a user needs to select several options and we need to send the EXACT object back with the modified (selected) components.

Improve this question
2
  • "We'd never interact with the serialized field just the ID" -- What? Commented Oct 12, 2011 at 14:18
  • 2
    Sorry its difficult to explain, I mean we'd never want to order or search the field. I was trying to avoid other instances of almost the same question where people wanted to perform actions on said field. Commented Oct 12, 2011 at 14:19

3 Answers 3

Reset to default
6

  1. Yes storing it as a text field (type TEXT) is correct but you can also store it in binary (type BLOB) if you're worried about character encoding.

  2. No, if you are only ever looking up by ID which is a primary key then it shouldn't have too much of a performance effect.

  3. If you're using MySQL to store the ID's, you might as well store the serialized object in the table, unless you have huge amounts of serialized data that could use up lot's of disk space but then storing it in the file system would have the same problem.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Field that's TEXT also stores character encoding information which isn't desirable for actions such as storing serialized object. It should be field that doesn't store encoding information, such as BLOB or VARBINARY.
3

Firstly;

Would it be right to store the serialization as a text type field?

Yes, I suggest you take a binary encoding for it. Blob might be better fitting then, which is text IIRC. Just prevent any sort of encoding, just store it binary.

Secondly;

If we do not directly interact with the serialized field other than r/w will it affect the performance of our database at all?

Not more as if it would be any other data.

Finally;

Would it be better to store the serialized object in our filesystem instead?

As those are mostly smaller chunks of data, I would prefer the database as it can handle better smaller chunks of data than a file-system that is having one file per serialized chunk.

Improve this answer

Comments

3

This sounds like a simple key->value setup -- one table, two columns, primary key on the first and no extra indexes. If your objects are large, MySQL isn't suited to this. If they're small, you're probably just fine. I'd think anything over around 12k is too large. InnoDB pages are 16k and if every entry with its overhead is taking up more than one page, you're starting to develop a mess.

File systems are good at handling this if you break objects up using adequate folder separation. They usually have issues if you have a million files in one directory. Where that tipping point is varies by filesystem, so you'll have to do some research. That means some custom code.

MongoDB is really suited to the key->value paradigm and is better at handling object storage than MySQL.

At the end of it, if we're talking less than 1,000,000 records and less than around 10gb total, just chuck it at MySQL and move on. I think at the point where it matters, you'll want to benchmark anyway.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.