MySQL vs MongoDB: Basic Differences between MySQL and MongoDB

MySQL vs MongoDB: Basic Differences between MySQL and MongoDB

MySQL and MongoDB are both free and open source databases. MySQL and MongoDB have a lot of basic differences in terms of data representation, querying, relationships, transactions, schema design and definition, normalization, speed and performance. By comparing MySQL with MongoDB, we are comparing Relational and non-relational databases. MongoDB is a scalable and high-performance open source database designed to handle document-oriented storage while MySQL is a widely used relational database. When building a custom web application, you need to consider the type of database that best suits your demand. If your are thinking to go with the best open source database, you will have to consider which database is best for you: MySQL or MongoDB? Here's a quick guide on the basic differences between MySQL (Relational) and MongoDB (Non-Relational / NoSQL). 

Data Representation

MySQL represents data in tables and rows.

MongoDB represents data as collections of JSON documents.

If you think about it, a JSON document is very much like what you would be working with in your application layer. If you are using javascript, it's exactly what you're working with. If you're using PHP, it's just like an associative array. If you're using python, its just like a dictionary object.

Querying

The SQL in MySQL stands for Structured Query Language. That's because you have to put together a string in this query language that is parsed by the database system. This is what makes SQL injection attacks possible.

MongoDB uses object querying. By that I mean you pass it a document to explain what you are querying for. There isn't any language to parse. If you're already familiar with SQL, it'll take a little bit of time to wrap your brain around this concept, but once you figure it out, it feels a lot more intuitive.

Relationships

One of the best things about MySQL and relational databases in general is the almighty JOIN operation. This allows you to perform queries across multiple tables.

MongoDB does not support joins, but it does multi-dimensional data types such as arrays and even other documents. Placing one document inside another is referred to as embedding. For example, if you were to create a blog using MySQL, you would have a table for posts and a table for comments. In MongoDB you might have a single collection of posts, and an array of comments within each post.

Transactions

Another great thing about MySQL is its support for atomic transactions. The ability to contain multiple operations within a transaction and roll back the whole thing as if it were a single operation.

MongoDB does not support transactions, but single operations are atomic.

Schema Definition

MySQL requires you to define your tables and columns before you can store anything, and every row in a table must have the same columns.

One of my favorite things about MongoDB is that you don't define the schema. You just drop in documents, and two documents within a collection don't even need to have the same fields.

Schema Design and Normalization

In MySQL there is really isn't much flexibility in how you structure your data if you follow normalization standards. The idea is not to prefer any specific application pattern.

In MongoDB, you have to use embedding and linking instead of joins and you don't have transactions. This means you have to optimize your schema based on how your application will access the data. This is probably pretty scary to MySQL experts, but if you continue reading, you'll see there is a place for both MySQL and MongoDB.

Performance

MySQL often gets blamed for poor performance. Well if you are using an ORM, performance will likely suffer. If you are using a simple database wrapper and you've indexed your data correctly, you'll get good performance

By sacrificing things like joins and providing excellent tools for performance analysis, MongoDB can perform much better than a relational database. You still need to index your data and the truth is that the vast majority applications out there don't have enough data to notice the difference.

When should you use MySQL?

If your data structure fits nicely into tables and rows, MySQL will offer you robust and easy interaction with your data. If it's performance that is your concern, there is a good chance you don't really need MongoDB. Most likely, you just need to index your data properly. If you require SQL or transactions, you'll have to stick with MySQL.

When should you use MongoDB?

If your data seems complex to model in a relational database system, or if you find yourself de-normalizing your database schema or coding around performance issues you should consider using MongoDB. If you find yourself trying to store serialized arrays or JSON objects, that's a good sign that you are better off MongoDB. If you can't pre-define your schema or you want to store records in the same collection that have different fields, that's another good reason.

Conclusion

You probably thought this was going to be all about performance, but MySQL and MongoDB are both tremendously useful, and there are much more important differences in their basic operations than simply performance. It really comes down to the needs of your specific application.