MongoDB vs MySQL

Integrity Model

MongoDB: BASE MySQL: ACID

What is MongoDB?

* Most poplar document-oriented databases * offers greater efficiency and reliability * schema-free implementation of MongoDB

What is MySQL?

* open-source relational database management system RDBMS * stores data in rows and tables * uses SQL * transfers data and commands such as "Select, Update, Insert, and Delete" * uses Join operations * horizontal scaling is not easy

When to use MongoDB?

If the following are your requirements, you should be using MongoDB: When you need high availability of data with automatic, fast and instant data recovery. In future, if you're going to grow big as MongoDB has in-built sharding solution. If you have an unstable schema and you want to reduce your schema migration cost. If you don't have a Database Administrator (but you'll have to hire one if you're going to go BIG). If most of your services are cloud-based, MongoDB is best suitable for you, as its native scale-out architecture enabled by 'sharding' aligns well with horizontal scaling and agility offered by cloud computing.

When to use MySQL?

If the following are your requirements, you should be using MySQL: If you're just starting and your database is not going to scale much, MySQL will help you in easy and low-maintenance setup. If you've fixed schema and a data structure aren't going to change over the time like Wikipedia. If you want high performance on a limited budget. If high transaction rate is going to be your requirement (like BBC around 30,000 inserts/minute, 4000 selects/hour) If data security is your top priority, MySQL is the most secure DBMS.

Security Mode: MongoDB

MongoDB: This 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.

MongoDB: Pros & Cons

MongoDB is best when you want the flexibility of schema. You can easily use replica sets with MongoDB and can take advantage of scalability. Expansion plans are flexible and can be easily achieved by adding more machines and RAM to the system. It also includes document validations and integrated systems. The cons of MongoDB include higher data size over the period of time. Due to the lack of atomic transactions, the speed is comparatively low compared to NoSQL. Also, the solution is quite infant and hence cannot replace the legacy systems directly.

Programming Language

MongoDB: C, C++, Java MySQL: C, C++, C#

CAP

MongoDB: CP MySQL: CA

Atomicity

MongoDB: Conditional MySQL: Yes

Schema

MongoDB: Flexible MySQL: Rigid

Data storage

MongoDB: JSON MySQL: Column and Rows

Replication mode

MongoDB: Leader - Follower MySQL: Leader - Leader / Follower

Developers:

MongoDB: MongoDB Inc. MySQL: Oracle Corporation

Graph Support

MongoDB: No MySQL: No

Isolation

MongoDB: No MySQL: Yes

Referential Integrity

MongoDB: No MySQL: Yes

Transactions

MongoDB: No MySQL: Yes

Query lang.

MongoDB: Volatile memory file system MySQL: SQL

Horizontal Scalability

MongoDB: Yes MySQL: Conditional

Creation/Development

MongoDB: Yes MySQL: No

Database conversion

MongoDB: Yes MySQL: No

Geospatial indexes

MongoDB: Yes MySQL: No

Performance analysis

MongoDB: Yes MySQL: No

Queries

MongoDB: Yes MySQL: No

Relational interface

MongoDB: Yes MySQL: No

Virtualisation

MongoDB: Yes MySQL: No

Backup

MongoDB: Yes MySQL: Yes

Composite Keys

MongoDB: Yes MySQL: Yes

Consistency

MongoDB: Yes MySQL: Yes

Data Migration

MongoDB: Yes MySQL: Yes

Durability (data storage)

MongoDB: Yes MySQL: Yes

Full text search

MongoDB: Yes MySQL: Yes

Monitoring

MongoDB: Yes MySQL: Yes

Replication

MongoDB: Yes MySQL: Yes

Secondary INdexes

MongoDB: Yes MySQL: Yes

Sharding

MongoDB: Yes MySQL: Yes

Sharding nothing architecture

MongoDB: Yes MySQL: Yes

Unicode

MongoDB: Yes MySQL: Yes

MapReduce

MongoDB: Yes MySQL: no

OS

MongoDB: multi platform MySQL: multi platform

Cloud, SaaS, Web

MongoDB: yes MySQL: yes

Sharding: MongoDB (in-depth)

MongoDB: Its sharding has the ability to break up a collection into subsets of data to store them across multiple shards. This allows the application to grow beyond the resource limits of a standalone server or replica set. Also, it can handle the distribution of data across any number of nodes to maximize the use of disc space and dynamically load balance queries. It also empowers users with automated failure and redundancy. Like MySQL, MongoDB sharding has the ability to perform range-based data partitioning. MongoDB also supports automatic data volume distribution and transparent query routing. A MongoDB sharded cluster consists of shard, mongos (query router) and config servers (store metadata and configuration settings). Mongo is very good in some areas where relational databases are particularly weak, like application-unaware scaling, auto-sharding, and maintaining flat response times even as dataset size balloons. While sharding in MongoDB is standardized, database architects should keep in mind the following considerations:- Carnality - Choose a shard key which is easy to split later if the database size is exceeding chunk size. Distribution - Sharding key should spread in a uniform distribution to avoid unbalanced design. Query - Each of your queries will result in a single shard key if any of the queries have the shard key. Otherwise, it will generate queries for each shard.

Performance & Speed: MongoDB

MongoDB: One single main benefit it has over MySQL is its ability to handle large unstructured data. It is magically faster. People are experiencing real world MongoDB performance mainly because it allows users to query in a different manner that is more sensitive to workload.

Replication: MongoDB (in-depth)

MongoDB: This supports only master-slave replication. It uses replica sets to create multiple copies of the data. Each member of the replica set will be assigned primary or secondary role at any point in the process. By default, read/writes are done on primary replica and then replicated on the secondary replicas.

MySQL: Pros & Cons

MySQL is around the block for a long time. One of the main pros is that it's community driven. Being a mature solution, it supports JOIN, atomic transactions with privilege and password security system. With MySQL, you may end devoting a lot of time and efforts which other platforms might do automatically for you, like incremental backups. The main issue with MySQL is scalability. No inbuilt XML and OLAP.

Sharding: MongoDB (in-depth)

MySQL: At some point in time, you need to scale your MySQL database. And you will opt for replication. Though, it is quite easy to add read scale to MySQL but what about write scale? Here comes the difficult part! Multi-master replication can add additional write scale but only for separate applications, each application must write to different master to get that scale. Unlike MongoDB, MySQL there is no standard sharding implementation. Though MySQL offers two ways for the sharding ie. MySQL Cluster - built-in Automatic sharding functionality and MySQL Fabric - official sharding framework, they are rarely deployed. The common practice is to roll your own sharding framework as Facebook and Pinterest have done. Database administrators need to take the design decision on the choice of sharding key, schema changes and mapping between sharing the key, shards and physical servers. The wrong choice in sharding key will lead to system inflexibility. For example, parent/child relationship between tables won't be automatically maintained if the parent and child rows are in separate databases. Thus the use of intelligent sharding keys or hashed sharding keys is critical. Also, sharding key changes can have a knock-on effect on application, data location, and transactionality across nodes. Data inconsistency can creep in if shards have not completed their schema changes. Also, schema changes require coordination across multiple separate MySQL instances which exposes the application to potential errors and downtime. Creating and maintaining the mapping between sharding keys, data partitions, databases and nodes is very essential. The changes in mapping due to shard splits, shard migrations, instance replacement, and sharing key changes should be done in a very fast lookup.

Performance & Speed: MySQL

MySQL: Developers note that MySQL is quite slow in comparison to MongoDB when it comes to dealing with the large database. Hence, it is a better choice for users with small data volume and is looking for a more general solution as it is unable to cope with large and unstructured amounts of data.

Replication: MySQL (in-depth)

MySQL: This supports both master-slave replication and master-master replication. Multi-source replication gives you the ability to replicate data from several masters in parallel. In master-slave replication, consistency is not too difficult as each piece of data has exactly one owing master. While in master-master replication, if you can make it work, they seem to offer everything you want with no single point of failure.

Security Mode: MySQL

MySQL: This 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. Though, 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 the SSL.

Sharding Term

Sharding is the method of distributing data across multiple machines to support deployments with large data sets and high throughput operations. High query rates and datasets larger than system RAM can put stress on I/O capacity of disk drives and CPU of the server. These problems are solved by horizontal and vertical scaling. Vertical scaling involves increasing the capacity of a single server by adding more RAM, powerful CPU, or storage space. Horizontal scaling includes dividing the dataset and load over multiple additional servers. Each machine handles a part of the workload with a comparatively lower cost than high-end hardware for a single machine.