MySQL

Summary

Let’s do something different.  I’m going to install the latest version of MySQL, which is version 5.7, which is advertised to be 3x faster.  I’m going to compare some basic database operations between the 32 bit version of MySQL, with the 64 bit version of Microsoft SQL Server 2014 (unfortunately, the MySQL msi is only in a 32 bit version).  I’ll be using Entity Framework to communicate with my basic CRUD operations.

Database Servers

Choosing a database server to build your software around is not very critical when you start a small project.  There are free versions of MS SQL, MySQL and Oracle that you can use.  There are also cheap versions that you can use with commercial projects until your software grows to enterprise level.  At the point where you need high-availability, the licensing costs of these servers can be very critical to the cost of doing business.  Unfortunately, there is no easy way to convert between these database engines.  I have converted between Oracle and MS SQL server in a rare setup involving no stored procedures.  Once a critical mass of stored procedures have been written the game is over.  You’re locked in.  So keep this in mind when you choose which database you use and how you construct your system.  You may be forced to change technologies in the future.

With that in mind, I want to test MySQL against MS SQL and see how they compare.  I will not be doing any stored procedures.

The Test Data

I’m going to do a very simple test to get a feel for the differences between these two databases.  First, I’m not going to use any indexes.  Second, I’m going to use only two simple tables: Department, with one field and Person, with three fields.  I’ll do one test with a foreign key constraint and one test without.  I’ll add an index to the foreign key of the person table in each database.  Both sets of tests will use EF 6.0 and I’ll use the same table objects for both databases.  Only the context will be different.

The first set of numbers shows the fastest timing for each operation of 5 tests with no foreign key applied to the person table.  The second set of data shows the same tests with a forign key applied.  There is definitely an advantage to using MS SQL Server, especially for select operations.  I want to also note that I set the change tracking to off for the select operations for both SQL and MySQL.  

To create the tables in MySQL use the following SQL statement: 

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first` varchar(50) DEFAULT NULL,
  `last` varchar(50) DEFAULT NULL,
  `department` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_department` (`department`),
  CONSTRAINT `fk_department` FOREIGN KEY (`department`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=436451 DEFAULT CHARSET=utf8;

To Create the tables in MS SQL:

CREATE TABLE [dbo].[Department](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Person](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [first] [varchar](50) NULL,
    [last] [varchar](50) NULL,
    [department] [int] NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [fk_department] FOREIGN KEY([department])
REFERENCES [dbo].[Department] ([id])
GO

ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [fk_department]
GO

Conclusions

This is a very small test and should be not be used as evidence to use one database server over another.  If you are making a decision on which server to use, I would recommend expanding the tables used in this sample to include other field types.  I would also recommend attempting other query types, such as outer joins and sub-queries.  If you have a small application written to use one data server or the other (and you don’t have any stored procedures), then you can copy your app and tweak the context to point to the other database.  Then generate a new database and insert some test data for comparison.  See how your application performs.   

One other test I would perform is to setup an Oracle instance and see how it compares with these in a side-by-side test.  The performance difference could be huge, but I would prefer something real-world over any claims made by the companies promoting their products.

Where to Get the Source Code

As always, I have uploaded the source code to my GitHub account.  You can download it by clicking here.  Make sure you fix up any context strings before running the tests.  The MySQL section was setup to require a password.  You’ll have to match that with the password you set in your data server.

Leave a Reply