MongoDB vs. MySQL – what is better for your project and why? We can see that the main operating storage and some additional services are used. A common example is caching or full-text search.
Another approach to the architecture of different databases is microservices that can have their own database. They are better optimized for the tasks of this particular service. For example, the main repository can be on MySQL, Redis and Memcache for caching and Elastic Search or native Sphinx for searching. And we’ll need something like Kafka to transfer data to the analytics system that is often done on something like Hadoop.
If we are talking about the main operating repository, we will have two options. On the one hand, we can choose relational databases with SQL. On the other hand, we can have something non-relational and then look at the subspecies that are available in this case.
If we talk about the NoSQL data model, then there is a lot to choose from. The most common are either key value, or document, or wide column database. Examples: Memcache, MongoDB, Cassandra respectively.
Why do we compare MySQL and MongoDB? In fact, there are several reasons. If you look at Ranking databases, then you’ll see that MySQL is the most popular relational database and MongoDB is the most popular non-relational database. Therefore, it is reasonable to compare them.
MongoDB initially actively focused on MySQL users. Therefore, very often people have experience of using and choosing between these two technologies.
What to choose: MySQL and MongoDB
What are the advantages of these systems?
If we talk about MySQL, this is a well-known technology. It is clear that MySQL is used by large companies for more than 15 years. Since it uses the SQL standard, it is possible to easily migrate to other SQL databases if you want. There is a possibility of transactions. Complex queries, including analytics, are also supported.
From the point of view of MongoDB, here the advantage is that we have a flexible JSON-format of documents. For some tasks and for some developers it is more convenient than to suffer from the addition of columns in SQL-databases. Simple queries rarely make problems.
If you look at performance problems, they mostly arise when people write complex queries with JOIN in a heap of tables and GROUP BY. If there is no such functionality in the system, then it is more difficult to create a complex query.
MongoDB has simple scalability using the technology of shading. We usually solve complex queries on the side of the application. That is, if we need to do something like JOIN, we can go to select the data, then go to select the data from the links and then process them on the side of the application. For people who know the language of SQL, it looks somehow pathetic and unnatural. Development of the server applications is much easier than dealing with JOIN.
Development and application lifecycle
If we talk about applications where MongoDB is used and what they focus on is a very fast development. Because everything can be constantly changed, you do not need to constantly take care of the strict format of the document.
The second point is the data schema. Here you need to understand that the data always has a schema, the question is only where it is implemented. You can implement the data schema in your application because somehow you use this data. This scheme is implemented at the database level.
Very often if you have an application, only this application works with the data in the database. For example, we store data from this application in this database. The scheme at the application level works well. If we have the same data used by many applications, then it is very inconvenient and difficult to control.
What about the application lifetime? With MongoDB, it’s good to make applications that have a very limited life cycle. If we make an application that does not live long, for example, a site for running a movie or an olympiad. We lived several months after that, and this application is practically not used. If the application lives longer, then there is another question.
The data model depends very much on the application and experience of the team. If we compare them with each other, it is clear what we have. In MySQL, there’s a relational database. We can easily display links between tables using a relational database. By normalizing data, we can make the data changes happen atomically in one place. When the data is denormalized, we do not need to run and modify a bunch of documents with some changes.
Is it good or bad? The result is always a table. On the one hand, it’s easy, on the other hand, some data structures do not always fit well on the table, we may be uncomfortable with this work.
It’s all in theory. If we talk about the practical use of MySQL, we know that we often denormalize data, sometimes we use something similar for some applications: we store JSON, XML, or another structure in the application columns.
MongoDB has a data structure based on documents. The data of many web applications can be displayed very simply. Because if we store the structure as something like an associated application array, then it’s very easy for the developer to serialize it into a JSON document. Deploying this in the relational database on different labels is a tough task.
Example. We want to keep the contact list on the phone. It is clear that there’s data that is well placed in one relational table: Surname, Name, etc. But if you look at the phones or email addresses, then one person may have several. If such a file is stored in a good relational form, then it would be nice to store it in separate tables, then it’s all to collect JOIN, which is less convenient than storing it all in one collection where the hierarchical documents are located.
It should be said that this is all strictly relational theory as some databases support arrays. In MySQL, the JSON format is supported where you can put things like several email addresses.
It is interesting that between MySQL and MongoDB something is the same and different. For example, we are talking about databases in both cases, but what we call a table in a relational database is often called a non-relational collection. The fact that MySQL is a column, we have a field in MongoDB.
In terms of using JOIN, in MongoDB there is no such thing, it is a concept from a relational structure. We either create a built-in document that is close to the concept of denormalization, or we just save the document ID in a field, call it a link and then select the data we need.