Storing serialized objects in MySQL and performance

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)u开发者_开发问答pdate 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.

---

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.

---

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.

---

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.