Panoply Blog: Data Management, Warehousing & Data Analysis

MySQL vs Oracle: Key Differences Compared | Panoply

Written by Tarun Telang | May 22, 2018 12:30:00 PM

We use databases to store, organize, query, and process structured data in computer systems quickly and efficiently.

In this post, I will highlight how two leading databases, MySQL and Oracle, are different. Without delay, let's now jump into the details of MySQL and Oracle to analyze their critical technical differences.

Below I have compared MySQL and Oracle from the perspective of building enterprise-grade cloud and large-scale web applications. In general, this post assumes you're already familiar with database concepts.

MySQL database

Undoubtedly, MySQL is the most popular open-source relational database management system. The SQL in MySQL stands for Structured Query Language, defined by the ANSI/ISO SQL standard.

MySQL is free to install and is available under the GPL license for open-source projects.

However, a software vendor that wants to distribute MySQL software as part of its offering must enter into a commercial license agreement with Oracle.

Additionally, MySQL is part of the LAMP web development stack.

At this time of writing, version 8.0.26 is its more recent stable release.

Product variants of MySQL database

MySQL Community Server is free to install, whereas MySQL Standard Edition is licensed starting at $2,000 annually. While MySQL Standard Edition has the same software as MySQL Community Server, it's a paid service because it comes with support.

If you need further enterprise-grade support, tools, and certifications, go for MySQL Enterprise Edition, starting at $5,000 annually.

Lastly, the most premium offering is the MySQL Cluster Carrier Grade Edition. It is priced starting from $10,000 annually and comes with advanced features like:

  • clustering,
  • high availability,
  • scalability,
  • security,
  • backup,
  • and monitoring.

Refer to this list of features available in different editions for more info.

In addition to the number of server CPUs, pricing will depend on whether customers choose perpetual or annual subscription-based licensing.

The complete price list is available here.

Key customers of MySQL database

Many enterprises across multiple domains and industry verticals use MySQL databases:

  • Technology: Apple and NASA
  • Telecom: AT&T Wireless
  • Social Network: Facebook, GitHub, and YouTube
  • Web applications: Airbnb, Uber, and WordPress
  • Manufacturing: GE, Toyota, and Tesla

You can find a complete list of MySQL database customers here.

Oracle database

Initially, Oracle Database was designed as an object-oriented relational database management system. But over time, it has become a multi-workload DBMS with support for data in formats like:

  • relational,
  • in-memory,
  • sharded data,
  • key/value pairs,
  • graph,
  • spatial data,
  • XML,
  • JSON,
  • free text,
  • and different types of documents.

Oracle Database is a converged database, as it has a single integrated back end and supports multiple data models. It can reside on a private, public, or hybrid cloud computing platform as a cloud database that customers can host without buying dedicated hardware.

Product variants of Oracle Database

At the entry-level, we have Oracle Express Edition, which comes with limited features as the free version. It's mostly used for learning purposes.

Next, we have the Oracle Database Personal Edition, which is priced at $460. It is for single-user environments but provides full compatibility with other editions.

Thirdly, we have Oracle Database Standard Edition 2, which is priced starting at $350. It is ideal for usage by teams in departments or for small web applications.

At the most premium level, we have Oracle Database Enterprise Edition, which starts at $950 and is built to support essential applications.

The complete price list is available here and for further details, refer to the database section in the Oracle Help Center.

Key Customers of Oracle database

Thousands of companies across various industry verticals are using Oracle Database:

  • Manufacturing: Unior and IFFCO
  • Finance: AsiaPay
  • Health care: San Javier Hospital
  • Education: University of Oxford
  • Hospitality: Starwood and Kingold

For a complete list of Oracle Database customers, click here.

Supported platforms by MySQL and Oracle

MySQL 8.0 and Oracle 19c databases support the following platforms:

Platforms

MySQL 8.0

Oracle 19c

Windows

macOS 10

Solaris 11 (SPARC)

Oracle, Red Hat Enterprise, CentOS Linux

Ubuntu, SUSE, Debian Linux

IBM AIX / HP-UX ia64 / Linux on System z

Virtualization

Docker

 

Go to MySQL's website for a complete list of its supported platforms, for Oracle, visit its website.

Differences in database limits between MySQL and Oracle

Database limits are crucial to consider while choosing the database for your application.

Below are the database limits for each service as per Oracle documentation, whose links are provided below:

Features

MySQL

Oracle

Maximum table size (default)

256 TB

128 TB

Maximum number of rows

65K

Unlimited

Maximum number of columns per table

1017

1000

Maximum number of indexed columns

64

32

The maximum length of the index

3,072 bytes

Unlimited

Maximum number of joins

61

200

 

For more details about MySQL database limits, refer to the topic Optimizing Database Structure, and for Oracle database limits, refer to the Database Limits in the documentation.

Differences in technical capabilities between MySQL and Oracle

Features

MySQL

Oracle

Paid support

Yes, Oracle Premium Support

Yes, several paid support options

Memory (RAM) usage

Low (about 1 MB)

High (minimum 128 MB)

Minimum disk usage

Low (1 GB)

High (10 GB)

Programming languages support

Modern languages like C/C++, Java, Python, Node.js, etc.

Modern and legacy languages (including FORTRAN and COBOL)

Development tools

MySQL Workbench

Oracle Application Express (APEX), Oracle SQL Developer, Oracle Spatial Studio

Management tools

MySQL Workbench

Oracle SQL

Embedded systems support

Yes, it can run on IoT devices

No, it cannot run on embedded devices

SQL support

Yes (with many extensions and non-SQL features)

Yes (with proprietary PL/SQL extension)

NoSQL interface support

Yes

Yes

Native JSON data type

Yes

No

Native API interfaces

Connector/ODBC, Connector/J, etc.

OCI (Oracle Call Interface), ODBC, etc.

Real-time in-memory performance

Yes, with the MEMORY storage engine

Yes, using Oracle Database In-Memory technology

Running complex queries

Yes, even on commodity hardware

Yes, only on specialized hardware

ACID/transactional Consistency (OLTP)

Yes (with InnoDB storage engine)

Yes

Locking

InnoDB supports row-level locking, while MyISAM supports only table-level locking

Yes, it supports several different locks, including row-level locks

Differences in advanced feature between MySQL and Oracle

The table below highlights the differences in the advanced feature between MySQL and Oracle databases.

Features

MySQL

Oracle

Security

Yes

Yes

LDAP/Active Directory support

No

Yes

Analytics and data warehousing (OLAP)

Yes, with the MyISAM storage engine

Yes, it supports analytics, Big Data, and data warehousing

Policy-based auditing

No, it provides minimal auditing capabilities

Yes, provides extensive policy-based auditing

Clustering

Yes, using MySQL Cluster technology, which provides clustering and auto-sharding

Yes, using Real Application Clusters (RAC)

Replication

Yes, can replicate databases and tables selectively

Yes

Backup and recovery

Yes

Yes, it supports online backup to prevent data loss

Multitenant/container database

No

Yes

Web scalability/horizontal scaling

Small to large systems

Medium to large-scale systems

High availability

Yes

Yes, through technologies like RAC, Data Guard, and Golden Gate

Portability

Easy to migrate

Challenging to migrate across multiple hardware and operating systems

Administration

Simple, as it provides many self-management features

Needs skilled and dedicated database administrators

Differences in SQL queries between MySQL and Oracle

Creating a database schema

Usually, the terms database and database schema are used interchangeably in the context of SQL.

The syntax used for creating a new database schema is the same for both MySQL and Oracle databases.

See the syntax below for your reference:

CREATE DATABASE <DatabaseName>

Here, <DatabaseName> denotes the argument representing the name of the new database schema to be created.

For example, this is how you would create a database of customers:

CREATE DATABASE sales

Creating a new table

Let's see how you would create a new table in each database.

Let's start with MySQL:

CREATE TABLE customer (

customer_id int PRIMARY KEY,

first_name varchar(50),

last_name varchar(50),

age int

)

The keyword PRIMARY KEY is used in the SQL query to mark the column as the primary key. The new table is getting created as a result of the execution of this CREATE TABLE SQL statement.

Now let's see an Oracle example:

CREATE TABLE customer (

customer_id int,

first_name varchar(50),

last_name varchar(50),

age int,

CONSTRAINT customer_pk PRIMARY KEY (customer_id)

)

In the above SQL statement, the CONSTRAINT keyword sets the column customer_id as the PRIMARY KEY.

As you can see, the difference between the syntax used in the above SQL query for defining the primary key, which is used to identify a row in the database uniquely.

Selecting records from the table

The syntax for selecting records from the table in MySQL is the same as that of Oracle.

See the query below for your reference:

SELECT * FROM customer

The above SQL query selects all the columns (denoted by *) while displaying the records from the customer table.

Inserting records in the table

Below is the example SQL query for inserting a record in the table of MySQL database.

You can use the same syntax in Oracle Database:

INSERT INTO customer(customer_id, first_name, last_name, age) VALUES ('001', 'John', 'Smith', 35)

Here, the VALUES keyword is used before the data denoting the row of the table. Executing this SQL query will add a new record to the customer table.

Updating records in the table

MySQL has the same syntax as Oracle for inserting a record in the table.

See the SQL query below for more details:

UPDATE customer SET first_name = "Marry", last_name = "Jones" WHERE customer_id = 1

Here, the SET keyword is used to set the values of the columns. The WHERE keyword filters the rows based on the condition where the value of the customer_id is 1.

Conclusion

As we have seen, both MySQL and Oracle databases come in multiple flavors, providing options ranging from cloud-based to free to develop/deploy/distribute.

Oracle is an advanced database suitable for use cases involving a large number of transactions and queries for data warehouses and Internet applications. It can even interface with older programming languages like COBOL and FORTRAN.

In general, to install an Oracle database, you would need specialized hardware, whereas MySQL can run on commodity hardware.

Oracle database provides full support for running a procedural language PL/SQL within it. Also, it comes with APEX, a low-code development platform, so you may not even need to install and manage a separate application server to run your business logic and perform data analysis.

On the other hand, with MySQL, you need additional applications to perform data analysis and separate web servers to run your business logic.

Lastly, you may find the Oracle database more suitable for running ERP applications like SAP or E-Business Suite, as they are traditionally better optimized with Oracle databases.

MySQL database is ideal for internet applications and services, as it is simple to manage, very efficient, reliable, and scalable. Further, MySQL also comes as a multi-threaded library to run on IoT devices.

To sum it up, both databases are battle-tested in the field; there is no clear winner.

To make the right choice, you should first understand the exact requirements of your application use cases and evaluate them in detail as per the technical and functional parameters covered in this post.

This series of posts analyzes how specific databases stack up against each other. We've covered the following comparisons so far: