MySQL vs. MongoDB: Looking At Relational and Non-Relational Databases

When building a custom web application you need to consider the type of database that best suits the data.  Here’s a quick guide on the differences between MySQL (Relational) and MongoDB (Non-Relational / NoSQL).

It was back in 2004 that Ruby on Rails first came out and popularized web application frameworks. What you might not know, is that it also popularized ORM (Object-Relational Mapping) layers with its ActiveRecord object. An ORM layer basically provides an object oriented interface to a relational database. That means that instead of writing a query to insert or update a record, you assign some properties to an object and call a save method. Instead of writing queries with joins, you can access related data through properties of an object.

For example, if you have a “post” object that represents a blog post, you can access it’s comments through the property “post.comments”. At first appearance, this might seem delightfully convenient, and to some extent it is. Fast forward a few years, and just about every web application framework out there implements an ORM layer. It lets you access relational data in a way that is convenient for your application. The problem, however, is that this is horribly inefficient and it teaches developers to ignore how the underlying database works. In fact, it’s so inefficient that it just about killed relational database systems altogether.

Thankfully, we never jumped on to the ORM bandwagon. Since then, many alternative database systems have been released. One of my favorites is MongoDB. Let’s look at some of the differences between MongoDB and a relational system like MySQL.

Data Representation

MySQL represents data in tables and rows.
Mysql table structure
  • Facebook
  • Twitter
  • LinkedIn
  • Google+
  • Evernote
MongoDB represents data as collections of JSON documents.
 
  • Facebook
  • Twitter
  • LinkedIn
  • Google+
  • Evernote
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.

Comments

  1. Utdweb Analytics says

    who has written this article? can i know your name so that i can use it for citing reference. please reply

  2. WinifredBig says

    Hi. I have checked your neonrain.com and i see you’ve got some duplicate content so
    probably it is the reason that you don’t rank hi in google.
    But you can fix this issue fast. There is a tool that rewrites content like human, just search in google: miftolo’s tools

  3. hill climb racing 2 apk says

    Thanks on your marvelous posting! I actually enjoyed reading it, you are a great author.I will always bookmark your blog and will eventually come back at some point. I want to encourage you continue your great work, have a nice holiday weekend!

  4. hvh cheat says

    Another issue is that video games are typically serious naturally with the primary focus on knowing things rather than entertainment. Although, we have an entertainment element to keep children engaged, each and every game is usually designed to focus on a specific expertise or course, such as math or scientific discipline. Thanks for your article.

  5. hack ngôi sao bộ lạc says

    I’ve learn several good stuff here. Definitely worth bookmarking for revisiting. I surprise how a lot effort you place to create this type of excellent informative web site.

  6. keyword research 2019 says

    Search engine optimization is one of the most discussed and controversial topics on the internet today. Also, it is one of the most misunderstood topics. In its basic form, search engine optimization (SEO) is act of altering a web site so that it does well in the organic, crawler-based listings of search engines. Now even this quick to the point definition of search engine optimization (SEO) can lead to spirited debates about the meaning and purpose of SEO. So the question maybe asked, why is SEO such a popular and controversial topic that causes so much debate within the SEO community and the general internet population particularly website owners? In my humble opinion there are 6 reasons why Search Engine Optimization (SEO) has the popularity that it currently enjoys. 1. Targeted Website Traffic – If you ask the vast majority of website owners and people within the SEO industry what is the most important factor to a successful website business and/or operation. A large percentage of the people will say website traffic and even more specifically targeted website traffic. Currently on the internet, the largest resource for targeted website traffic on a continuous bases are search engines. On a yearly bases stats show that around 75 – 80 of all purchases made by individuals on the internet originally started as a result of a search at a search engine and the vast majority of people who visit websites usually come via way of a search engine. Numbers so dominate cannot be ignored even by the biggest opponents of search engine optimization. Moreover, because SEO is focused on optimizing websites for ranking success related to search engines, it is only natural that people would be interested in the field and practice of search engine optimization (SEO).

  7. corelcad says

    Thanks for revealing your ideas. I’d also like to mention that video games have been actually evolving. Better technology and revolutions have aided create realistic and interactive games. Most of these entertainment video games were not as sensible when the actual concept was being tried. Just like other designs of technological innovation, video games also have had to advance by way of many many years. This itself is testimony for the fast growth of video games.

  8. eebest8 back says

    “Thanks , I have just been looking for information about this subject for a while and yours is the greatest I’ve found out so far. However, what about the conclusion? Are you certain concerning the supply?”

  9. fiverr says

    I felt very happy while reading this site. This was really very informative site for me. I really liked it. This was really a cordial post. Thanks a lot!.

  10. Hire .Net App Developer in Australia says

    iPad App Development Services: A Competitive Move! • We develop Mobile Apps for all types and sizes of companies and businesses that wish to grow and generate New Growth Opportunities and Increase their Sales through the following Services: • Development of Native Mobile Apps for Smartphones and Tablets • Development of Multiplatform Applications and Web-apps • Web Page Design • Professional Graphic Design • Multimedia Design , Animation and Videogames Services Learn more about our offer of products and services in Mobile App Development , Magento web design and Graphic and Multimedia Design. Top 5 Reasons on Why Your Business Should Opt for iPhone App Development Services

  11. 바카라사이트추천 says

    Nice contribution. It also helps me for my student and I have suggested this blog for English learning.
    Thanks, that’s very kind of you! I appreciate your reading my work!

  12. vn hax pubg says

    I’m impressed, I have to admit. Genuinely rarely should i encounter a weblog that’s both educative and entertaining, and let me tell you, you may have hit the nail about the head. Your idea is outstanding; the problem is an element that insufficient persons are speaking intelligently about. I am delighted we came across this during my look for something with this.

  13. JacobErron says

    You might also want to suggest more research or comment on things that it wasn’t possible that you discuss in the paper. The writing profession includes many perks. In the event you have any fiscal essay writing problem, let’s know for we will aid you with writings which are quality and which are free from plagiarism.
    You ought not be worried because our college essay writing firm would be the best way to buy college essay services that are perfectly tailored. Apparently, a badly written article reflects the sort of service which you offer.
    Content writing can also be a kind of essay writing, only you must be careful using the rules, if you feel that it’s possible to write essay correctly then readily you may also write the articles, it’s not in any manner a massive thing. It is the chief part of the prewriting procedure of an essay.
    Writing a thesis statement demands great intelligence from the surface of the essay author as it ought to specify the basic notion of the novel. Writing an essay is a challenging issue to perform to get a student and also for a standard man who doesn’t have the particular comprehension of the language and the grammar that ought to be utilised within an essay.
    Following the pupil doesn’t have a personal opinion, then they ought to simply earn a choice to choose a subject, and choose pro or con. Very very good essay writers possess the capacity to give aid to their students whenever it’s required.
    Students utilizing a copywriting service should know about a couple things before choosing a service. After going through the company advice and terms and conditions, if you are pleased with their solutions, you may pick a specific small business. The writing service must additionally have a warranty that all work is original and distinctive from many other content.
    how to start an argumentative essay introduction example

  14. data togel hongkong says

    This is where the concept memory comes in, because it’s like the foam remembers your bodyshape, allowing you the perfect rest every single period.7 said they want media items (books, CDs, video games) and 35.While they can bend to the shape that you liein it, it’s more ‘general’ rather then the specificsupport that memory foam can do.

Leave a Reply

Your email address will not be published.