MongoDB vs. MySQL

Imagine finding a DBMS that aligns with tech goals of your organization. Pretty exciting, right?

Relational databases held the lead for quite a time. Choices were quite obvious: MySQL, Oracle or MS SQL, to mention a few. Though times have changed pretty much with the demand for more diversity and scalability, haven't they?

There are many alternatives in the market to choose from, though I don’t want you to get all confused again. So how about a faceoff between two dominant solutions that are close in popularity?

MongoDB vs MySQL?

Both of these are some of the most popular open-source database software.

On that note, let’s get started.

Flexibility of Schema

One of the best things about MongoDB is that there are no restrictions on schema design. You can just drop a couple of documents within a collection and it isn’t necessary to have any relations between those documents. The only restriction with this is supported data structures.

But due to the absence of joins and transactions (which we will discuss later), you need to frequently optimize your schema based on how the application will be accessing the data.

Before you can store anything in MySQL, you need to clearly define tables and columns, and every row in the table should have the same column.

And because of this, there isn’t much space for flexibility in the manner of storing data if you follow normalization.

For example, if you run a bank, its information can be added to the table named ‘account’ as follows:

This is how MySQL stores the data. As you can see, the table design is quite rigid and it is not easily changeable. MongoDB stores the data in the JSON type manner as described below:

Such documents can be stored in a collection as well.

MongoDB creates schemaless documents which can store any information you want though it may cause problems with data consistency. MySQL creates a strict schema-template and hence it is bound to make mistakes.

Querying Language

MongoDB uses an unstructured query language. To build a query in JSON documents, you need to specify a document with properties you wish the results to match.

It is typically executed using a very rich set of operators that are linked to each other using JSON. MongoDB treats each property as having an implicit boolean AND. It natively supports boolean OR queries, but you must use a special operator ($or) to achieve it.

MySQL uses the structured query language SQL to communicate with the database. Despite its simplicity, it is indeed a very powerful language which consists mainly of two parts: data definition language (DDL) and data manipulation language (DML).

Let’s have a quick comparison.

Relationships in MongoDB and MySQL

MongoDB doesn’t support JOIN — at least, it has no equivalent. On the contrary, it supports multi-dimensional data types such as arrays and even other documents. The placement of one document inside another is known as embedding.

One of the best parts about MySQL is the JOIN operations. To put it in simple terms, JOIN makes the relational database relational. JOIN allows the user to link data from two or more tables in a single query with the help of single SELECT command.

For example, we can easily obtain related data in multiple tables using a single SQL statement.

This should provide you with an account number, first name, and the respective branch.

Performance and Speed

One single main benefit it has over MySQL is its ability to handle large unstructured data. It is magically faster because it allows users to query in a different manner that is more sensitive to workload.

Developers note that MySQL is quite slower in comparison to MongoDB when it comes to dealing with large databases. It is unable to cope with large and unstructured amounts of data.

As such, there is no “standard” benchmark that can help you with the best database to use for your needs. Only your demands, your data, and infrastructure can tell you what you need to know.

Let’s look at a general example to understand the speed of MySQL and MongoDB in accordance with various functions.

Measurements have been performed in the following cases:

• MySQL 5.7.9

• MongoDB 3.2.0

Each of these has been tested on a separate m4.xlarge Amazon instance with Ubuntu 14.4 x64 and default configurations; all tests were performed for 1,000,000 records.

Performance

It is evident from the above graph that MongoDB takes way more lesser time than MySQL for same commands.

Security Model

MongoDB uses a role-based access control with a flexible set of privileges. Its security features include authentication, auditing, and authorization.

Moreover, it is also possible to use Transport Layer Security (TLS) and Secure Sockets Layer (SSL) for encryption purposes. This ensures that it is only accessible and readable by the intended client.

MySQL uses a privilege-based security model. This means it authenticates a user and facilitates it with user privileges on a particular database such as CREATE, SELECT, INSERT, UPDATE, and so on.

But it fails to explain why a given user is denied specific access. On the transport layer, it uses encrypted connections between clients and the server using SSL.

When to Use MongoDB or MySQL

This infographic explains when you'd use MongoDB over MySQL and vice versa.

Conclusion

To answer the question, “Why I should use X over Y?” you need to take into consideration your project goals and many other things.

MySQL is highly organized for its flexibility, high performance, reliable data protection, and ease of managing data. Proper data indexing can resolve your issue with performance, facilitate interaction and ensure robustness.

But if your data is not structured and complex to handle, or if predefining your schema is not coming easy for you, you should better opt for MongoDB. What’s more, if you're required to handle a large volume of data and store it as documents, MongoDB will help you a lot!

The result of the faceoff: One isn’t necessarily better than the other. MongoDB and MySQL both serve in different niches.

If you enjoyed this article and want to learn more about MongoDB, check out this collection of tutorials and articles on all things MongoDB.