MySQL or MongoDB: Which Database System should you use?

MySQL or MongoDB: Which Database System should you use?

A database system is a software application that maintains a relationship with other applications, the end users and the database itself for the purpose of capturing and analysing data, including defining the data, creating, querying, updating and administering the databases. It is a very systematic process of managing and retrieving data whenever required. Main features include (a) the capacity to manage large amounts of data, (b) excellent storage with backup facilities, (c) easy-to- use interface language (SQL), (d) excellent security management and (e) providing concurrent, multi-user support.

There are different kinds of database systems in the market, and the price and performance depends on what kind of features you expect from it. The ability of the database system to differentiate between the different kinds of data in the multiple file systems and servers, the amount of data that can be stored and retrieved and other vendor-specific optimisations can all be decided during the selection process.

Relational database management system, one of the most common models around is based on the relational model invented by Edgar F. Codd, of IBM’s San Jose Research Laboratory.

Both MySQL and MongoDB are two of the major players in relational databases in the market, and the demand for faster time to market, and better developer productivity have to led to the choosing of either of the two.

MySQL

MySQL is an open source relational database management system developed, distributed and managed by Oracle. You can pre-define your database schema as per your requirements and create rules that would define the relationships between the fields in your tables. It stores data in table formats (a collection of related data entries) and uses SQL or Structured Query Language for database access. SQL was initially created in the 1970s, so it was not originally created for database management. SQL is an ANSI or American National Standards Institute, but there are several versions of it.

MongoDB

MongoDB is also an open source non-relational database developed by MongoDB, Inc. The data is stored as documents in the binary format called BSON or Binary JSON. Information that can be grouped together are stored together for easy and quick retrieval. The use of dynamic schemas is one of the major advantages of MongoDB, and this eliminates the need to predefine the structures. MongoDB allows for hierarchical relationships representation and the possibility to change the records structure by simply adding/deleting fields.

Companies that use MySQL – Netflix, YouTube, Pinterest, Twitter, Spotify, US Navy, NASA, Walmart, and Paypal.

Companies that use MongoDB – T-Mobile, Sony, SurveyMonkey, Foursquare, Klout, Citrix, Twitter, Zendesk, Hootsuite, MuleSoft, and InVision.

Comparison between the two in various scenarios

Database Structure

MySQL

The data is stored in tables, and uses SQL to access the data. It uses strict schemas for defining the database structure. All the rows within the table possesses the same structure, wherein the values are represented in a particular data type.

MongoDB

The schemas are dynamic, while the data is stored in JSON-like documents, with no particular structure. You can create the documents themselves without the need to define the structure However, related data is stored together, and this helps in query speed.

The need for indexes

Both the database systems have provision for indexes

MySQL

The database engine will have to scan the entire table to find the corresponding rows.

MongoDB

Without the help of indexing, each and every document will have to be scanned minutely to see which one matches the query statement. And this could be painstaking and time consuming.

The differences in the Query statement
The process of selecting, inserting and updating the records are different.

Observe it below:
While selecting records from the customer table:

MySQL: SELECT * FROM customer

MongoDB: db.customer.find()

While inserting records into the customer table:

MySQL: INSERT INTO customer (cust_id, branch, status) VALUES (‘appl01’, ‘main’, ‘A’)

MongoDB: db.customer.insert({ cust_id: ‘appl01’, branch: ‘main’, status: ‘A’ })

The process of updating records in the customer table:

MySQL: UPDATE customer SET branch = ‘main’ WHERE custage > 2

MongoDB: db.customer.update( { custage: { $gt: 2 } }, { $set: { branch: ‘main’ } }, { multi: true } ).

Level of risk:

MySQL is more vulnerable as it can be subject to SQL injection attacks.

MongoDB doesn’t have a language to parse, and it uses object querying, so the level of risk is considerably reduced.

Deploying the databases: Where and How?

MySQL
Written in C and C++, MySQL has binaries for Microsoft Windows, Linux, AIX, OS X, NetBSD, BSDi, HP-UX, FreeBSD and IRIX.

MongoDB
Written in Javascript, C and C++, MongoDB has binaries for the following platforms – Linux, OS X, Solaris, and Windows.

Additionally, there are methods through which you can consolidate data from MySQL, MongoDB and others into a single data management platform.

Support-level provided

MySQL provides Oracle Lifetime Support on the basis of three plans – Premier, Extended and Sustain. Premier is chosen by versions 1 to 5 years old, Extended is suitable for versions 6 – 8 years old, and Sustain for people with versions for 9+ years. There is 24/7 support, flexibility to upgrade to other versions, updates, security fixes and so on.

MongoDB

MongoDB provides Enterprise Grade Support, with security fixes, constant upgrades, unlimited support 24/7, extended life-cycle with add-ons, flexibility to upgrade whenever you need it and so on.

Conclusion

So which is the right database for your business?

Now comes the 100 billion dollar question. Should you go for MySQL or MongoDB? You can choose MySQL, if you need multi-row transactions because they work better with relational databases. For example, Accounting system. MongoDB would not work optimally here because they cannot replace legacy systems.

There are a number of situations where MongoDB is really suited. They include real-time analytics, the internet of things, content management, mobile, and several new kind of applications and so on. You can also choose MongoDB when you have databases with no clear schema definition or if you cannot define the schema for the database. With this database system, you can easily store unstructured data, so it can be retrieved and updated easily.

You can also go for MongoDB if you have no database administrator, of if you have to work in an environment where you cannot have reliable connectivity to other servers. It is also the ideal choice when the write load is high. As it was designed to have a high insert rate, there are no longer worries about transaction safety.

Interesting Articles:
Comparison between both, by MongoDB
Which one would you choose MongoDB or MySQL?

Picture Source: Flickr.com/ Nozell/ Garrett

The author: Reema Oamkumar is engaged as a thought leader at www.Software-Developer-India.com which is a part of the YUHIRO Group. YUHIRO is a German-Indian enterprise which provides programmers to IT companies, agencies and IT departments.