Skip to content

Instantly share code, notes, and snippets.

@kirilkirkov
Last active November 2, 2015 13:01
Show Gist options
  • Save kirilkirkov/4fb35cffafdcc1b087e4 to your computer and use it in GitHub Desktop.
Save kirilkirkov/4fb35cffafdcc1b087e4 to your computer and use it in GitHub Desktop.
Relational and Non-Relational Databases (MySQL vs. MongoDB)
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).
###Data Representation
-MySQL represents data in tables and rows.
-MongoDB represents data as collections of JSON documents.
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.
### What is MySQL? ###
MySQL is a popular open-source relational database management system (RDBMS) that is developed, distributed and supported by
Oracle Corporation. Like other relational systems, MySQL stores data in tables and uses structured query language (SQL) for
database access. In MySQL, you pre-define your database schema based on your requirements and set up rules to govern the
relationships between fields in your tables. In MySQL, related information may be stored in separate tables, but associated
through the use of joins. In this way, data duplication is minimized.
### What is MongoDB? ###
MongoDB is an open-source database developed by MongoDB, Inc. MongoDB stores data in JSON-like documents that can vary in
structure. Related information is stored together for fast query access through the MongoDB query language. MongoDB uses
dynamic schemas, meaning that you can create records without first defining the structure, such as the fields or the types of
their values. You can change the structure of records (which we call documents) simply by adding new fields or deleting
existing ones. This data model give you the ability to represent hierarchical relationships, to store arrays, and other more
complex structures easily. Documents in a collection need not have an identical set of fields and denormalization of data is
common. MongoDB was also designed with high availability and scalability in mind, and includes out-of-the-box replication and
auto-sharding.
### Terminology and Concepts ###
Many concepts in MySQL have close analogs in MongoDB. This table outlines some of the common concepts in each system.
MySQL MongoDB
Table <--> Collection
Row <--> Document
Column <--> Field
Joins <--> Embedded documents, linking
### Query Language ###
MySQL
INSERT INTO users (user_id, age, status)
VALUES ("bcd001", 45, "A")
MongoDB
db.users.insert({
user_id: "bcd001",
age: 45,
status: "A"
})
MySQL
SELECT * FROM users
MongoDB
db.users.find()
@kirilkirkov
Copy link
Author

untitled

@kirilkirkov
Copy link
Author

Tutorial for using MongoDB with php:
http://php.net/manual/en/mongo.tutorial.php

@kirilkirkov
Copy link
Author

Ubuntu installation and using simple:
https://www.youtube.com/watch?v=VNie_VWqimA

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment