MongoDB vs MySQL: Complete Tutorial

  • Home
  • /
  • Blog
  • /
  • MongoDB vs MySQL: Complete Tutorial

Today we’re going to explore the differences between MongoDB and MySQL. First, a question: are the two locked in mortal competition? Not at all. In fact, “MongoDB vs SQL” makes the issue sound more adversarial that reality reveals since the two serve very different purposes by design. Another important FAQ is this: Is MongoDB faster than MySQL? Again, this is like comparing basketball and baseball: they’re different games, but you might say they both have balls!

By this, I mean that they are both data stores, but they store and retrieve data in completely different ways if the two are used for their ideal intended purposes. Although many blogs hype SQL as a “legacy” db, SQL server will always have its unique advantages, including the fastest query for structured tabular relational data.

In this article, we’re going to discover the advantages and disadvantages of MongoDB tools and MongoDB data modeling, and then do the MongoDB performance comparison via MongoDB monitoring tools within the platform from MySQL to Mongo and from MongoDB to MySQL, so let’s get rolling.

SQL vs NoSQL

MongoDB is referred to as a “NoSQL” database, which literally tells us that it is a different breed of animal from SQL. SQL is not a MongoDB alternative unless you’re specifically looking for referential enforcement and other features of the pre-existing data structure. MongoDB vs MySQL performance benchmarks needs to compare a Mongo database query against SQL query speed, for example.

SQL will naturally beat MongoDB performance for structured data such as row and column based tabular data. However SQL cannot represent polymorphic data, which is the special talent of the MongoDB query language and MongoDB big data structures, so there is no direct comparison on this score, and so the MongoDB vs MySQL comparison results in more in feature differences rather than MongoDB vs MySQL performance comparisons and metrics.

While we’re on the subject of speed, MongoDB vs PostgreSQL leads more to a feature analysis, because PostgreSQL specializes in compliance standards. On the other hand, MongoDB speed vs MySQL benchmarking contains a particular nuance because here we are comparing web app queries strictly speaking; whereas Oracle SQL running as a desktop app is leagues faster than web DBs from MySQL to MongoDB. To gain full insight into MongoDB MySQL performance issues, let’s start by understanding the basic mechanics and methods of each.

SQL Nuts and Bolts

MySQL made its debut in 1995 and is the de facto standard RDBMS for web apps. MySQL follows the design logic of Oracle and MS SQL Server methods and other implementations with regard to the ubiquitous standard SQL query language. MySQL is a highly structured relational database, meaning that a dataset has a strictly tabular schema, and data tables are created and manipulated by use of the JOIN statement in the SQL language.

Tables are indexed on key fields which are the relational pivot points in a dataset. Oracle SQL Servers are astonishingly fast for the very reason that the structure of the database is fully known and the machine level encoding is uninhibited.

A great advantage of SQL is to JOIN various datasets on a key field and SELECT data from each. MongoDB database design, on the other hand, is less consistent in structure and therefore relational queries require more engineering. This aspect about MongoDB means that traditional RDBMS developers will think carefully about how to create MongoDB structures, and when to use MongoDB.

MongoDB Nuts and Bolts

A Mongo database, on the other hand, is a collection of documents structured more loosely and in the BSON format, meaning binary JSON, which is the JavaScript Object Notation. Therefore, all JavaScript data types are naturally supported in MongoDB.

Furthermore, this makes MongoDB server highly interactive with Node.js coded server side apps. But since documents are stored in the JSON textual format there is a performance sacrifice in MongoDB MySQL comparisons because SQL servers encode data for enhanced query performance. The JSON like the format of MongoDB datasets are fairly human-readable, by contrast to the tabular or Excel-like structure of SQL databases.

One of the best features offered by MongoDB is the use of dynamic schemas that eliminates the need to fully define the structure in advance – the meaning of polymorphism – meaning that fields or value types can morph on the fly.

Models of this kind thus support hierarchical relationships in representation, as well as array storage, and also the ability to modify record structures simply by adding or deleting fields. This NoSQL solution comes with embedding, auto-sharding, and on-board replication for better scalability and high availability.

MongoDB or MySQL – Pros and Cons

Traditional RDBMS platforms build relationships in key fields, but MongoDB is not inherently based on such relationships. Let’s explore the rich advantages of MongoDB. Here are some favorite functionalities:

  • Free of strict schema
  • Diverse document database
  • Collections based, Non Tabular structure
  • The collection contains different documents
  • Variable number of fields
  • Rich content / size capability
  • Clear single doc structure (BSON)
  • Free from complex joins.
  • Deep query support – dynamic queries
  • MongoDB commands language
  • Easy and high scalability
  • Conversion and mapping freedom

MongoDB Best Practices Tutorial

Right now we’re going to start MongoDB with a pretty basic tutorial. There is a mountain of MongoDB documentation to digest when you’re ready to take the plunge into MongoDB getting started. First, install and open your MongoDB interface. We create databases in MongoDB lazily. You don’t need to create databases with explicit commands as in SQL. Instead, the MongoDB driver system creates a db automatically when the need is implied by your actions. For example, to create a new database just enter the use command and switch to a new database folder. Afterward, any command which saves data will result in the creation of a new database. For example:

use films
switched to db films
db.drama.insert({name:"Pride and Prejudice", year:1986})

These two commands will result in the creation of anew database named “films”. The second command results in the creation of a new collection in the db called “drama” within in the database “films.” Now for the basic acronym which describes the fundamental actions in a MongoDB.

CRUD – The Basics

Now that we are rolling with a new db, let’s look at several examples to Create, Read, Update, and Delete objects within our db. First let’s create a couple of new documents in the collection:

db.drama.insert({name:"The English Patient", year:1989})
db.drama.insert({name:'Wonder Boys', year:1996})

This insert command has the effect of adding two more documents to the collection named “drama” in the “films” database. Here, we take notice of the use of these particular words within MongoDB: document and collection. The save command action is similar. The insert always adds a new document, whereas save adds one if there is no _id key already in the object, otherwise, it does the update action. To read a sample record, use the find command like this:

> db.drama.find().limit(1)
{ "_id" : ObjectId("4e9e0ef412"), "name" : "Wonder Boys", "year" : 1996 }

A real object _id is a longer string than the example above. Operators in the MongoDB command language can be used to filter or select documents conditionally. In our simple collection of two records we can get all films prior to the year 1995 by using the less than (<) operator $lt within the find command like this:

> db.comedy.find({year:{$lt:1995}})
> db.drama.insert({name:"The English Patient", year:1989})

For an exact match, simply specify the year. Filter and match operators can be quite sophisticated using Regex expressions as well. To include only the name field from the doc, specify it in this way as:

> db.drama.find({year:1989}), {name:true})

We can search nested objects in MongoDB collections, meaning objects within objects, by simply using the regular JavaScript dot notation within the target object as key and then quoting the search text. There is really no limit to the ability to search within collections and documents. Arrays can be searched and filtered numerically and per element. Interior array objects can be searched and filtered with JS dot notation but this requires a specification when objects in an array have the same key but differing values. Here, we use the $elemMatch operator. Since HTML form request values return strings it is easiest to use a JavaScript expression to search strings in web apps. Otherwise, convert an input value to a numerical type like this:

var rating = +params.rating;

Params.rating is a MongoDB string type value, but the variable rating is an actual JavaScript numerical type, which can then be used within a MongoDB query command. The cost of using native JavaScript in MongoDB commands is speed: running JS within Mongo slows execution somewhat.

Take precaution when updating MongoDB documents to avoid accidentally overwriting fields. For example:

> db.drama.update({name:"Wonder Boys"}, {director:'Cuthbert Splevins'})

While this appears sensible, it will overwrite the document with the single director field. To add fields to an existing document, let’s use the $set command within the update like this:

> db.drama.update({name:"Wonder Boys"}, {'$set':{director:'Cuthbert Splevins', cast:['Michael Douglas', 'Spider Boy']}})

Although the existing fields may be reordered, the document data are preserved, and here we have demonstrated adding a field and its new data as well. Now, to update a field whose value is within an array structure, we use $push within the update like this:

> db.drama.update({name:"Wonder Boys"}, {'$push':{cast:'Katie Porter'}})

To complete our four-step CRUD tutorial, we need delete commands. Let’s start by deleting a field from all the documents within a collection. Here is an example:

> db.drama.update({$unset:{cast:1}}, false, true)

The parameter false is an unset option, while true is used to set the multiple options. The purpose of the multiple options is to declare the delete from the entire collection. To remove a single doc from its collection, simply specify the name as:

> db.drama.remove({name:’Wonder Boys’})

Above we have deleted all documents with name Wonder Boys. Now that we have full control of the basics of CRUD ops we can move on to a more interesting topic. So let’s explore a particular data structure in MongoDB.

A Tree Structure Example

Collections don’t enforce document structure as SQL does. The freedom to design a database structure can have extraordinary repercussions. Decisions affecting the data model design in MongoDB are crucial to application performance. Mastery of db design concepts is required if the performance is to be optimal. Database capacity is also impacted by design. This concept of data modeling, in other words, is built into the MySQL primitives, and the coder simply learns and uses it. The Database engine and SQL interpreter enforce the structure. Not every coder is a master of db structural design. So this is a very important decision when choosing to start a project with MongoDB.

Let’s look at an example data model, a typical tree hierarchical structure, in MongoDB. This db of book titles is a broadly usable structure type for many kinds of data modeling. Let’s enter the following data which also generates a structure:

db.categories.insert( { _id: "Titles", parent: 0, left: 1, right: 12 } )
db.categories.insert( { _id: "Coding", parent: "Titles", left: 2, right: 11 } )
db.categories.insert( { _id: "Languages", parent: "Coding", left: 3, right: 4 } )
db.categories.insert( { _id: "DB", parent: "Coding", left: 5, right: 10 } )
db.categories.insert( { _id: "MongoDB", parent: "DB", left: 6, right: 7 } )
db.categories.insert( { _id: "dbm", parent: "DB", left: 8, right: 9 } )

Notice how each new level refers a parent level. This can have unlimited branches and is highly convenient for representing many kinds of data. How do we query a tree structure’s data? Here is a typical query:

var databaseCategory = db.categories.findOne( { _id: "DB" } );
db.categories.find( { left: { $gt: databaseCategory.left }, right: { $lt: databaseCategory.right } } );

This document describes a data model that defines a tree-like structure that optimizes searching subtrees. Here, we have a nested sets pattern ID for each node in the tree which looks at each node in a full traversal of the tree structure. The application looks at each node in the tree two times, first during the initial traversal, and then again in a second traversal. The nested sets pattern saves every node in a document along with the tree node and parent ID, left and right fields. The MongoDB profiler writes all the data it collects to the system.profile collection, a capped collection in the admin.

Mongolithic Enhancements

Data in MongoDB, unlike SQL, has a flexible schema, which contributes positively to MongoDB scalability. This along with MongoDB support count toward the overall MongoDB benchmark. In fact, MongoDB vs MySQL benchmarking typically favors MongoDB especially for Big Data applications, because Big Data tends to be polymorphic (although this is somewhat incongruent with the need for linear datasets in machine learning). The area of MongoDB vs SQL server performance contains complexity. Sharding, replication, versioning are all significant issues when doing performance metrics on servers. MongoDB backup functionality is provided as configurable settings in admin.

MongoDB vs SQL Performance In Web Apps

With built-in spatial functions, a study comparing MongoDB analytics with that of a SQL found useful results. Databases with spatial extensions were studied for PostGIS use in two problems with spatial and aggregate queries and across a diverse range of datasets and including varying feature counts within the datasets. MongoDB, as well as other NoSQL databases in general, are found to be better suited for simultaneous multiple-user query environments.

prev
next