MySQL vs Oracle

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

In this MySQL vs. Oracle post, we'll see how MySQL stacks up against Oracle's database engine. We'll compare the key technical differences between MySQL and Oracle: indexes, queries, performance, replication, and clustering - everything you need to know about the differences between MySQL and Oracle.

Who Uses These Databases?

Here are some companies that use these databases:

MySQL: GitHub, US Navy, NASA, Tesla, Netflix, WeChat, Facebook, Zendesk, Twitter, Zappos, YouTube, Spotify. The full list is here.

Oracle: Bauerfeind AG, CAIRN India, Capcom Co., ChevronTexaco, Coca-Cola FEMSA, COOP Switzerland, ENEL, Heidelberger Druck, MTU Aero Engines, National Foods Australia, Spire Healthcare, Stadtwerke München, Swarovski, Tyson Foods, TVS Motor Company, Vilene. The full list is here.

Database Structure: Differences and Similarities

MySQL: MySQL is an open-source relational database management system (RDBMS). Just like other relational databases, MySQL uses tables, constraints, triggers, roles, stored procedures and views as the core components that you work with. A table consists of rows, and each row contains data for each column. MySQL uses primary keys to uniquely identify each row (a.k.a record) in a table, and foreign keys to assure the referential integrity between two related tables.

Oracle: Oracle is a multi-model database with a single, integrated back-end.  This means that it can support multiple data models like document, graph, relational, and key-value within the database. 

What is a Database Index? 

Indexes enhance database performance, as they allow the database server to find and retrieve specific rows much faster than without an index. But, indexes add a certain overhead to the database system as a whole, so they should be used sensibly.

Without an index, the database server must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more costly the operation.

MySQL: Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions include indexes on spatial data types that use R-trees. MySQL also supports hash indexes and the InnoDB engine uses inverted lists for FULLTEXT indexes.

Oracle: Oracle supports creating an index on:

  • One or more columns of a table, a partitioned table, an index-organized table, or a cluster.
  • One or more scalar typed object attributes of a table or a cluster.
  • A nested table storage table for indexing a nested table column.

Oracle Database supports several types of indexes:

  • Normal indexes - by default Oracle creates B-tree indexes.
  • Bitmap indexes - which store rowids associated with a key value as a bitmap.
  • Partitioned indexes - which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table.
  • Function-based indexes - which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
  • Domain indexes - which are instances of an application-specific index of type indextype.

How Are Their Queries Different?

MySQL queries are, in most cases, exactly the same as Oracle queries.

Creating a customer database

Create a customer database in MySQL:

Create a customer database in Oracle:

Creating a customer table

Create a customer table in MySQL:

CREATE TABLE customer (
cust_id int PRIMARY KEY,
branch varchar(255),
status varchar(255));

Create a customer table in Oracle:

CREATE TABLE customer (
cust_id int,
branch varchar(255),
status varchar(255),
CONSTRAINT customer_pk PRIMARY KEY (cust_id));

Selecting records from the customer table

SELECT * FROM customer;

In Oracle:
SELECT * FROM customer;

Inserting records into the customer table

INSERT INTO customer(cust_id, branch, status) VALUES ('appl01', 'main', 'A');

In Oracle:
INSERT INTO customer(cust_id, branch, status) VALUES ('appl01', 'main', 'A');

Updating records in the customer table

UPDATE customer SET branch="main" WHERE custage > 2;

In Oracle:
UPDATE customer SET branch="main" WHERE custage > 2;

Where (And How) Are These Databases Deployed?

MySQL: MySQL was written in C and C++. It has support for the following programming languages: C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, Erlang.

Oracle: Oracle was written in Assembly language, C, and C++. It has support for the following programming languages: Java, .NET, C, C++, Node.js, Python, PHP, Go, R, Ruby, Ruby on Rails, Perl, Erlang, Rust, COBOL, FORTRAN.

Both databases are available on multiple operating systems, but Oracle dominates the database world in part because it runs on more than 60 platforms, everything from mainframes to Apple Macs.

What Types Of Replication / Clustering Are Available?

Replication is a process that enables you to have multiple copies of the data copied automatically from 'master' to 'slave' databases. There are multiple benefits, including:

  • Simplifying backups.
  • Spreading the load to improve performance.
  • An analytics team can work on one of the slave databases, and not impact the performance of the main database in case of long-running and intensive queries.

Clustering, in the context of databases, refers to using shared storage and using multiple database front-ends. The front-end servers share an IP address and cluster network name that clients use to connect, and they decide between themselves who is currently in charge of serving client requests.

MySQL: Replication in MySQL is one-way asynchronous replication where one server acts as a master and others as slaves. You can replicate all databases, selected databases or even selected tables within a database.

MySQL Cluster is a technology providing shared-nothing (no single point of failure) clustering and auto-sharding (partitioning) for the MySQL database management system.

Internally, MySQL Cluster uses synchronous replication through a two-phase commit mechanism to guarantee that data is written to multiple nodes. Contrast this with what is usually referred to as "MySQL Replication", which is asynchronous.

Oracle: Oracle Streams is a built-in feature of the Oracle database that enables data replication and integration. Its flexible infrastructure meets a wide variety of information sharing needs. Oracle Streams enables the propagation of data, transactions, and events in a data stream either within a database or from one database to another.

Oracle Real Application Clusters (Oracle RAC) comprises multiple interconnected computers or servers that appear as if they are one server to end users and applications. Oracle RAC uses Oracle Clusterware for the infrastructure to bind multiple servers, so they operate as a single system. Oracle Clusterware is a portable cluster management solution integrated with Oracle database.

Who's Currently Behind The Databases?

MySQL: MySQL's source code is available under the terms of the GNU General Public License, and under a variety of proprietary agreements. MySQL was purchased by Oracle Corporation in 2008. Oracle's proprietary licenses provide additional features such as support for Oracle Enterprise Manager.

Notable forks include MariaDB and Percona Server.

Oracle: the Oracle database is a proprietary database produced and marketed by Oracle Corporation. There are several different versions, from cloud-based to a free to develop / deploy / distribute; Oracle Database Express Edition.

Who Provides Support and is There a Community Around It?

MySQL: MySQL has community support options, as well as commercial ones.

Oracle: Oracle also has community support, and several paid support options from Premier Support to web-based support with MyOracle.

Who Maintains The Documentation?

MySQL: MySQL has official and community-based documentation available.

Oracle: Oracle maintains the Help Center with useful information including getting started guides and advanced features.

Very useful community sites are the omnipresent StackOverflow and a bit more database-specific Stack Exchange for Databases.

Which Database is Right for Your Business?

If you're looking for a market leader solution that has proven itself in many different industries and you need premium technical support, then you should choose Oracle. However, be aware of the costs and that there are some differences from vanilla SQL, such as the Oracle proprietary PL/SQL extension.

By showing you the technical differences between MySQL and Oracle, the differences in queries, how each database addresses replication, clustering, and other common database functionality, we hope we’ve helped you as you compare MySQL vs. Oracle. 

Get a free consultation with a data architect to see how to build a data warehouse in minutes.
Request Demo
Read more in:
Share this post:

Work smarter, better, and faster with monthly tips and how-tos.