Mysql Vs. Mongodb - Relational And Non-Relational Databases

Building web application need some database to store user information and other data persistently. MySql is the most popular relational database. There is some sort of frameworks called ORM(Object-Relational Mapping) to insert, update and delete data into MySql database. It provides object-oriented interface to relational database. That is instead of writing queries with joins, CRUD(Create, Read, Update, Delete) can be done using properties of an object. For example, you have a blog post each is represented by a “post” object so that you can access it’s comments using the property of that object i.e. “post.comments”. It seems that this is great and easier but it is very inefficient way to do CRUD. Everytime you add a new layer of abstraction, some performance or efficiency loss occurs. Traversing multiple relationships such as “a.b.c.d” can be very slow as most ORM framework will be doing an independent databse query for each “.” Operation.

Mongodb is becoming very popular now a days and replacing MySql in some bigger web applications. It helps programmers and developers to build web apps faster, deploy easier and scale bigger. Modern applications are more networked, social and interactive. This needs to support big data, fast features development and flexible deployment strategies. Applications are storing more and more data and accessing them in higher and higher rate. If your database uses a single server, you will reach to scaling limit. Mongodb can scale by adding more servers, you can add more capacity whenever you want.

Lets compare Mongodb and MySql with common features as follows :

  1. Data Representation
  2. Quering
  3. Relationships
  4. Transactions
  5. Schema Defination and Design
  6. Performance

Data Representation

MySQL uses tables and rows to represent data whereas MongoDB represents data as collections of JSON documents. Documents are analogous to structures in programming languages. Actually MongoDB documents are BSON documents. BSON is binary representation of JSON with additional type information which can include advanced data formats like arrays or arrays of documents.

JSON format is very popular and no need to learn new techniques of manipulating them. You can use it directly from your application layer i.e. if you’re a using javascript, it’s not different from the JSON it uses. For PHP it’s like an associative array. These documents are stored in Collections. A collection is a group of related documents which is analogous to tables in relational database.

Quering

For quering possible, you have to put some string in query language processor so that it is parsed by database system to retrieve or insert data into database from where it got it’s name SQL (Structured Query Language). While quering, some malicious string can be passed to the database system which is well-known problem called SQL injection attacks.

MongoDB uses object querying which mean you pass it a document specifying some criteria, or conditions that identify the documents that MongoDB returns to the clients. A query may contain a projection along with optional conditions to impose limits, skips and sort orders that specifies the fields. There is not language parser so that is very performant and feels a lot more intuitive.

Relationships

The power of MySQL or relational database is JOIN operation which makes possible to perform queries across multiple tables. If you have a blog post with comments, it can be represented in MySQL in two tables “posts” and “comments”. Then MySQL queries both tables using JOIN (relationship operator) to get all the comments of that post.

MongoDB does not support JOIN but can store advanced data types called multi-dimensional arrays and documents. You can place one document inside another document which is referred to as embedding. Taking the above example of blog post with comments, MongoDB can store them in a single collection of posts with array of comments within each post. This is straight-forward method and very easier to understand.

Transactions

MySQL supports atomic transactions and are able to contain multiple operations within a transaction and roll back whole thing as if it were a single operation.

MongoDB does not support transactions, but does provide atomic operations on a single document. These document-level atomic operations are sufficient to solve problems that would require ACID transactions in a relational database. For example a single document can contain related data in nested arrays or nested documents and all of them can be updated in a single atomic operation. This way is more efficient than the way relational database use.

Schema Definition and Design

The irritating part of MySQL is that you have to define tables and number of columns before data can be stored on them. So it is not really flexible in terms of data structure and designing.

Whereas MongoDB don’t need any schema or fields, just create documents within a collection and you are ready to insert data into it. It uses embedding and linking so no need to use joins and transactions.

Performance

As you know most of popular memory-hungry CMS like wordpress, drupal are very slow and needs better server for them to run smoothly. The main bottlenecks of performance is MySQL. The database wrapper such as hibernate makes the CRUD operation more easier but they make performance worse.

Due to the absence of joins, transactions and providing excellent tools for performance analysis, MongoDB can perform lot faster than a relational database. You store data , organize and access it exactly the same way.

For example you have to store an advanced entity with dozens of tables to represent various properties with many indexes to ensure relational integrity between tables in MySQL. MongoDB can store the complex entity with different properties in a single document and in a single collection.

To retrieve the entity from database, MongoDB can fetch it by single id.

But MySQL have to lookup on the root table fetched by id, search for multiple tables for entity’s property values. These lookups involve random IO – tables may reside in different spots on disk, the rows in the tables may not be contiguous.

These are the reasons, MongoDB outperforms MySQL database.

Conclusion

MySQL and MongoDB both may have advantages and disadvantages because MySQL has been used for a long period of time and we know all it’s cons and pros whereas MongoDB is new in the world of database and need to experiment a lot with it for exact comparison between them.