Comparison Study of Two Open Source Databases: [617089]

Comparison Study of Two Open Source Databases:

MongoDB and MySQL

Iris Grossman

University of Oradea, Bihor,

Master Student: [anonimizat],

Universității Str., 410087 Oradea, Romania, E-Mail: [anonimizat]

Abstract
–
This
study
analyzes
the
differences
between

MongoDB
and
MySQL
based
on
factors
such
as

performance, relations, security, and more.

Keywords:
​
relational databases, MongoDB, MySQL

I. INTRODUCTION TO OPEN-SOURCE

DATABASES

Relational
databases
have
been
the
most
popular

choice
for
quite
some
time,
the
choices
being
obvious,

MySQL, Oracle or MS SQL.

This
has
changed
a
bit
as
the
demands
for
more
diversity

and
scalability
increased
over
the
years.
Although
there

are
many
alternatives
on
the
market
from
which
we
can

choose
from,
among
the
most
dominant
and
popular

solutions are MongoDB and MySQL.

The
biggest
advantage
of
the
two
is
that
both
are
free,

both being open-source.

A.
What is MongoDB?

MongoDB
is
a
document-oriented
open-source
NoSQL

database.
MongoDB
is
part
of
the
NoSQL
database

family.
It
was
developed
from
an
idea
started
in
2007

and
the
first
version
was
launched
in
2010
by
MongoDB

Inc., who is still maintaining it.

The
main
difference
is
that
data
storage
is
not
done

using
tables
as
in
a
relational
database.
MongoDB
stores

data as JSON documents with dynamic schemes.

MongoDB
is
written
in
C
++.
It
can
contain
multiple

databases,
collections
and
indexes.
In
some
cases

(databases
and
collections)
these
objects
can
be
created

by
default.
Once
created,
they
can
be
found
in
the

db.systems.collection
amd
db.system.indexes.
The

collections
contain
documents
(BSON).
These

documents
also
contain
several
fields.
In
MongoDB

there
are
no
predefined
fields
unlike
relational

databases,
where
there
are
columns
that
are
defined

when
the
tables
are
created.
There
is
no
schema
for
the

fields
in
a
document,
they
and
their
types
can
vary.
Thus

there
is
no
"alter
table"
operation
for
adding
columns.
In

practice
it
is
common
for
a
collection
to
have
a

homogeneous
structure,
although
it
is
not
a
requirement,

the
collections
may
have
different
structures.
This

flexibility means ease in migrating.

This
is
why
MongoDB
is
frequently
used
for
Node.js

projects.
It
also
offers
better
efficiency
and
reliability,

making
it
easier
to
cope
with
increased
storage
capacity

and speed improvement requests.

In
addition,
the
"schema-free"
implementation
of

MongoDB
eliminates
the
conditions
necessary
to
define

a
fixed
structure.
These
models
allow
hierarchical

representation
of
relations
and
facilitate
the
ability
to

change the structure on the spot.

B.
What is MySQL?

MySQL
is
a
relational
database
management
system,

produced
by
the
Swedish
company
MySQL
AB,
who

were
acquired
by
Oracle,
and
distributed
under
the
GNU

General
Public
License.
It
is
the
most
popular

open-source
Database
Management
System
at
present,

being
a
key
component
of
the
LAMP
stack
(Linux,

Apache, MySQL, PHP).

Although
it
is
used
very
often
together
with
the
PHP

programming
language,
with
MySQL
you
can
build

applications
in
any
major
language.
There
are
many
API

schemes
available
for
MySQL
that
allow
you
to
write

applications
in
many
programming
languages
​​
to
access

MySQL
databases,
such
as:
C,
C
++,
C
#,
Java,
Perl,

PHP, Python, FreeBasic, etc.

However,
the
limitations
of
MySQL
are
the
same
as
for

relational
databases.
Millions
of
read
/
write
instructions

severely
affect
performance
and
therefore
scaling
is
not

easy enough.

Although
replication
and
grouping
are
available,
the

basic
problems
of
relational
database
design
are
more

complicated.

C.
Implementation and community

MongoDB:
Currently
owned
and
developed
by

MongoDB
Inc.
It
is
extremely
easy
to
implement.
It
is

also
available
for
SaaS,
Cloud
and
Web
applications
and

can
run
on
multiple
platforms,
including
Linux,

Windows and MacOS.

MongoDB
attracts
users
with
its
clean
and
simple

philosophy,
not
just
with
its
collaborative
and
helpful

community.

MySQL:
Currently
owned
and
developed
by
Oracle

Corporation.
MySQL
can
be
installed
manually
from
its

own source code.

It
is
available
for
SaaS,
Cloud
and
Web
applications
and

can
run
on
multiple
platforms,
including
Linux,

Windows
and
MacOS.
An
advantage
of
MySQL
is
its

age,
which
has
led
to
the
formation
of
a
strong

community.

D.
MySQL vs. MongoDB terms

MySQL

MongoDB

Database

Database

Table

Collections

Index

Index

Fow

BSON Document

Column

BSON Field

Join

Embedded documents

and linking

Primary Key

Primary Key

Group by

Aggregation

E.
Query Language in MongoDB and MySQL

MongoDB:
Uses
an
unstructured
query
language.
To

create
a
query
in
JSON
documents,
you
must
specify
a

document
with
properties
that
you
want
the
results
to

match.

It
is
usually
performed
using
a
very
large
set
of

operators,
connected
to
each
other
using
JSON.

MongoDB
treats
each
property
as
having
a
default

Boolean
"AND".
It
natively
accepts
Boolean
"OR"

queries, but you must use a special operator ($ or).

MySQL:
Uses
SQL
Structured
Query
Language
to

communicate
with
the
database.
Despite
its
simplicity,
it

is
indeed
a
very
powerful
language
that
consists
mainly

of
two
parts:
Data
Definition
Language
DDL
and
Data

Manipulation Language DML.

The
following
commands
can
be
used
to
query
the
data

in
the
MySQL
database
–
'SELECT',
'UPDATE',

'INSERT' and 'Delete'.

Fig. 1. Query Language in MySQL and MongoDB

F.
Relations in MongoDB and MySQL

MongoDB:
It
does
not
accept
JOIN
directly,
but
has

a
JOIN
equivalent
–
the
$
lookup
search
operator.
With

the
help
of
this
operator
it
is
possible
to
combine
data
in

a
single
query
from
several
documents.
And
therefore,

there is less code to write in the application.

MySQL:
One
of
the
best
parts
about
MySQL
is
the

JOIN
operations.
To
put
it
in
simple
terms,
JOIN
makes

the
relational
database
"relational."
JOIN
allows
the
user

to
connect
data
from
two
or
more
tables
in
a
single

query using a single "SELECT" command.

G.
Possibility of full-text search

MongoDB:
Full-text
search
was
not
possible
with

MongoDB
until
recently.
As
with
MySQL,
it
is
executed

using
a
specific
type
of
index
on
the
array
of
strings.

Moreover,
it
also
accepts
the
search
for
sentences
and

the search for terms.

Boolean
search
is
accepted
as
a
combination
of

search
for
terms
and
search
for
phrases.
It
is
an
easy
to

perform feature, although it has some limitations.

MySQL:
It
is
possible
to
search
full-text,
which
is

performed
using
a
special
type
of
index.
It
helps
that
it
is

using
natural
language
search
(phrase
search),
Boolean

search (term search) and query search.

However,
currently
full-text
indexing
is
not
supported
in

clustered MySQL databases.

H.
MongoDB vs MySQL: Performance and speed

MongoDB:
The
main
benefit
it
has
over
MySQL
is
its

ability
to
handle
unstructured
big
data.
It's
much
faster.

This
is
because
it
allows
users
to
query
in
a
different

way, which is more sensitive to workload.

MySQL:
Developers
note
that
MySQL
is
quite
slow

compared
to
MongoDB
when
it
comes
to
large

databases.
Therefore,
it
is
a
better
choice
for
users
with

low
data
volume,
who
are
looking
for
a
more
general

solution
because
they
are
not
able
to
cope
with
large
and

unstructured amounts of data.

I.
MongoDB: advantages and disadvantages

Pros:
MongoDB
is
a
better
choice
when
it
comes
to

scheme
flexibility.
MongoDB
replica
sets
can
be
easily

used
to
take
advantage
of
scalability.
Expansion
plans

are
flexible
and
can
be
easily
accomplished
by
adding

more
machines
and
RAM
to
the
system.
It
also
includes

document validation and integrated systems.

Cons:
MongoDB
involves
larger
data
sizes
over
time.

Due
to
the
lack
of
atomic
transactions,
the
speed
is

relatively
low
compared
to
NoSQL.
Also,
the
solution
is

fairly new and cannot replace previous systems directly.

J.
MySQL: advantages and disadvantages

Pros:
MySQL
has
been
around
for
a
long
time.
One
of

the
main
advantages
is
that
it
is
community
led.
Being
a

mature
solution,
it
accepts
JOIN,
atomic
transactions

with security system with privileges and password.

Cons:
With
MySQL,
we
can
spend
a
lot
of
time
and

effort
on
things
that
other
platforms
could
do

automatically.
The
main
problem
with
MySQL
is

scalability. XML and OLAP are not embedded.

K.
Testare

Comparative speed tests for MySQL and MongoDB in

GOLANG1.6 & PHP5.

System used: DELL cpu i5 4th gen 1.70Ghz * 4 ram

4GB GPU ram 2GB.

Comparison of RDBMS vs NoSQL speed for INSERT,

SELECT, UPDATE, DELETE executing different row

numbers: 10,100,1000,10000,100000,1000000.

GOLANG with MySQL

INSERT

Nr. of rows

Time

10

1.195444ms

100

6.075053ms

1000

47.439699ms

10000

483.999809ms

100000

4.707089053s

1000000

49.067407174s

SELECT

Nr. of rows

Time

1000000

872.709µs

SELECT & DISPLAY

Nr. of rows

Time

1000000

20.717354746s

UPDATE

Nr. of rows

Time

1000000

2.309209968s

100000

257.411502ms

10000

26.73954ms

1000

3.483926ms

100

915.17µs

10

650.166µs

DELETE

Nr. of rows

Time

1000000

6.065949ms

GOLANG cu MongoDB

INSERT

Nr. of rows

Time

10

2.067094ms

100

8.841597ms

1000

106.491732ms

10000

998.225023ms

100000

8.98172825s

1000000

1m 29.63203158s

SELECT

Nr. of rows

Time

1000000

5.251337439s

FIND & DISPLAY

Nr. of rows

Time

1000000

21.540603252s

UPDATE

Nr. of rows

Time

1

1.330954ms

PHP5 cu MySQL

INSERT

Nr. of rows

Time

10

0.00406800000001s

100

0.011595s

1000

0.049718s

10000

0.457164s

100000

4s

1000000

42s

SELECT

Nr. of rows

Time

num of rows

time taken

1000000

<1s

SELECT & DISPLAY

Nr. of rows

Time

1000000

20s

PHP5 cu MongoDB

INSERT

Nr. of rows

Time

10

0.065744s

100

0.190966s

1000

0.2163s

10000

1s

100000

8s

1000000

78s

FIND

Nr. of rows

Time

1000000

<1s

FIND & DISPLAY

Nr. of rows

Time

1000000

7s

UPDATE

Nr. of rows

Time

1000000

9s

L.
Conclusion

To
answer
the
main
question
"Why
should
we
use
X

over
Y?",
the
objectives
of
the
project
and
many
other

relevant things must be considered.

MySQL
is
highly
optimized
for
its
flexibility,
high

performance,
reliable
data
protection
and
ease
of
data

management.
Correct
indexing
of
data
can
solve
your

performance
problem,
facilitate
interaction
and
ensure

resilience.

But
if
the
data
is
not
structured
and
complex
to
manage,

or
if
we
do
not
want
to
predefine
the
structure,

MongoDB
is
a
better
solution.
Moreover,
if
it
is

necessary
to
manage
a
large
volume
of
data
and
store
it

as documents – MongoDB is the perfect solution.

One
is
not
necessarily
a
substitute
for
the
other.

MongoDB and MySQL both serve in a different niche.

M.
References

https://en.wikipedia.org/wiki/MySQL

https://en.wikipedia.org/wiki/NoSQL

https://en.wikipedia.org/wiki/MongoDB

https://www.mongodb.com

https://www.mysql.com/

MySQL vs MongoDB

https://blog.panoply.io/mongodb-and-mysql

https://www.simform.com/mongodb-vs-mysql-database/

https://mindmajix.com/mongodb-vs-mysql