mysql key/value store problem

Question

I'm trying to implement a key/value store with mysql

I have a user table that has 2 columns, one for the global ID and one for the serialized data.

Now the problem is that everytime any bit of the user's data changes, I will have to retrieve the serialized data from the db, alter the data, then reserialize it and throw it back into the db. I have to repeat these steps even if there is a very very small change to any of the user's data (since there's no way to update that cell within the db itself)

Basically i'm looking at what solutions people normally use when faced with this problem?

Answer 1

Maybe you should preprocess your JSON data and insert data as a proper MySQL row separated into fields.

Since your input is JSON, you have various alternatives for converting data:

You mentioned many small changes happen in your case. Where do they occur? Do they happen in a member of a list? A top-level attribute?

If updates occur mainly in list members in a part of your JSON data, then perhaps every member should in fact be represented in a different table as separate rows.

If updates occur in an attribute, then represent it as a field.

I think cost of preprocessing won't hurt in your case.