MySQL to MongoDB Migration Example

There’s a lot of buzz right no for noSQL solutions and one of the big ones is MongoDB. I was lucky enough to be sent to MongoNYC conference a month or so ago and it opened my eyes to why noSQL is the way to go for most web applications. As the web is more and more based on user contributions our databases are getting more writes put into them which doesn’t scale well in MySQL.

So Photoblog was suffering from slow database performance. The only way to solve this problem was scale up the hardware running it. Now that costs a lot of money so my idea was lets switch from MySQL to MongoDB! So below is an example of how I took 2 tables and made them into one collection in MongoDB.

So an example of our two tables in MySQL

members

CREATE TABLE `members` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL DEFAULT '',
  `password` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(60) NOT NULL DEFAULT '',
  KEY `email` (`email`),
  KEY `username_2` (`username`),
)

friends

CREATE TABLE `friends_ng` (
  `userLink` mediumint(8) NOT NULL,
  `friendLink` mediumint(8) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `reason` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `timestamp` int(15) NOT NULL,
  `start` tinyint(1) NOT NULL DEFAULT '0',
  KEY `friendLink` (`friendLink`,`userLink`,`status`),
  KEY `userLink` (`userLink`)
)

Now friends was a bit de-rationalized in the first place that is why i have the start column. I found this to be faster then having it completely rational.

So now the fun part is turning it into a MongoDB document to add to our user collection. For those not up to speed with MongoDB you can think of a collection in MongoDB like a table in MySQL and a document as a row. A document in MongoDB is made up of a json array.

So here is what we want to end up with. This is an example taken from my username document.

{
        "_id" : ObjectId("4c21058b7f8b9acf39020000"),
        "email" : "mike@zcentric.com",
        "friends" : [
                ObjectId("4c21058b7f8b9acf39000000"),
                ObjectId("4c21058b7f8b9acf39030000")
        ],
        "friends_pending" : [ ],
        "password" : ".........",
       "username" : "mikezupan",
}

So there are a few differences here.

• What are those _id’s?
• Why add friends and friends_pending?

Well _id’s in MongoDB are like unique values for referencing your document. Think of them as an auto-incrementing primary key in MySQL. Well except they aren’t auto-incremented they are more random.

Friends and friends_pending are now in the user object for easy look ups and they are a reference to the friends _id. My friends_pending is empty since I have no friends pending.

So now on to the fun stuff with pulling out data.

So a common query is list all the friends you have. I decided to do this in 2 queries since its a pretty cheap query for my dataset. There are a lot of ways of making a document better where you can do it in a single query but I like this way.

I will be doing this via the MongoDB shell but its the same for any MongoDB driver.

So lets first pull out the user based on username. Usually I’ll set their _id object in a session so I can pull out the user with that.

var u = db.users.findOne({'username': 'mikezupan'})

Now lets find all the users friends

db.users.find({'_id': {'$in': u['friends']}})

It is as simple as that! Have fun with MongoDB, I know I am in my free time.