Storing Data in MySQL as JSON

Question

I thought this was a n00b thing to do, and so i've never done it than i saw that FriendFeed did this and actually made their DB scale better and decreased latency. I'm curious if I should do this, and if so, what's the right way to do it?

Basically, whats a good place to learn how to store everything in MySQL as a couchDB sort of DB. Storing everything as JSON seems like it'd be easier and quicker (not to build, less latency).

Also, is it easy to edit/delete/etc things stored as JSON on the DB?

Answer 1

I would say the only two reasons to consider this are:

• performance just isn't good enough with a normalised approach
• you cannot readily model your particularly fluid/flexible/changing data

I wrote a bit about my own approach here:

http://stackoverflow.com/questions/2285045/what-scalability-problems-have-you-solved-using-a-nosql-data-store/2316921#2316921

(see the top answer)

Even JSON wasn't quite fast enough so we used a custom-text-format approach. Worked / continues to work well for us.

Is there a reason you're not using something like MongoDB? (could be MySQL is "required"; just curious)

Answer 2

CouchDB and MySQL are two very different beasts. JSON is the native way to store stuff in CouchDB. In MySQL, the best you could do is store JSON data as text in a single field. This would entirely defeat the purpose of storing it in an RDBMS and would greatly complicate every database transaction.

Don't.

Having said that, FriendFeed seems to use an extremely custom schema on top of MySQL. It really depends on what exactly you want to store, there's hardly one definite answer on how to abuse a database system so it makes sense for you. Given that the article is a year and a half old and their main reason against Mongo and Couch was immaturity, I'd re-evaluate these two if MySQL doesn't cut it for you. They should have grown a lot by now.

Answer 3

json characters are nothing special when it comes down to storage, chars such as

{,},[,],',a-z,0-9.... are really nothing special and can be stored as text.

the first problem your going to have is this

{ profile_id: 22, username: 'Robert', password: 'skhgeeht893htgn34ythg9er' }

that stored in a database is not that simple to update unless you had your own proceedure and developed a jsondecode for mysql

UPDATE users SET JSON(user_data,'username') = 'New User';

So as you cant do that you would Have to first SELECT the json, Decode it, change it, update it, so in theory you might as well spend more time constructing a suitable database structure!

I do use json to store data but only Meta Data, data that dont get updated often, not related to the user specific.. example if a user adds a post, and in that post he adds images ill parse the images and create thumbs and then use the thumb urls in a json format.