MySQL: Performance benchmark MyISAM versus InnoDB

Because I’m running a project which has a relative large database (tables with 70.000.000 records in them), I wanted to know which was faster in performance, MyISAM or InnoDB.

Currently I’m using MyISAM, and I’m running into some performance and stability issues.

First, I’ve created a table with a few columns:

CREATE TABLE `jointable` (
`pk_id` int(11) NOT NULL auto_increment,
`indexed_int` int(11) default NULL,
`indexed_char` varchar(255) default NULL,
`randchar` varchar(255) default NULL,
PRIMARY KEY  (`pk_id`),
KEY `idx_indexed_int` (`indexed_int`),
KEY `idx_indexed_char` (`indexed_char`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

As you can see, the table contains a primary key, an indexed integer column and a indexed varchar column.

The storage engine is MyISAM,  but later in the benchmark test I will convert the table to InnoDB.

I filled the table with 250.000 records.

Now I’m executing the following three queries, where I join the tables using the different fields:

-- Using the Primary key to join the tables
SELECT COUNT(t1.randchar), COUNT(t2.randchar)
FROM jointable t1
INNER JOIN jointable t2 ON t1.pk_id = t2.pk_id

-- Using the indexed integer column to join the tables
SELECT COUNT(t1.randchar), COUNT(t2.randchar)
FROM jointable t1
INNER JOIN jointable t2 ON t1.indexed_int = t2.indexed_int

-- Using the indexed char column to join the tables
SELECT COUNT(t1.randchar), COUNT(t2.randchar)
FROM jointable t1
INNER JOIN jointable t2 ON t1.indexed_char = t2.indexed_char

The results:

-- Results based on a 250.000 row recordset
__________________________________________________
|         | Primary Key | Int Index | Char index |
| MyISAM  | 3.09 sec    | 21.45 sec | 7.95 sec   |
| InnoDB  | 1.01 sec    | 18.48 sec | 19.09 sec  |
__________________________________________________

All the queries are executed with an empty MySQL cache (I restarted MySQL after every query).

Overall InnoDB is faster when joining data which have the integer datatype, only when you have ID’s based on the character datatype (for example GUID’s) InnoDB is slower than MyISAM.

It is recommended to use InnoDB, because on large databases (with tables with millions of records in it) the performance improvement will be even better!

Useful Links:
InnoDB project page
MyISAM project page
Wikipedia on InnoDB
Wikipedia on MyISAM
MySQL documentation about all the storage engines available

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.