Difference between MyISAM and InnoDB engines in terms of performance
Since most web projects use MySQL for the purpose of storing data, webmasters have to choose between InnoDB which supports foreign keys, row level locking, and MyISAM which was default engine until MySQL 5.5 was released. These two engines are used the most, however other engines may be used as well (MEMORY, Aria, CSV and so on). Let’s answer the question which is faster; MyISAM or InnoDB?
Prerequisites
Of course there should be a MySQL database installed on your machine. If you’re missing it, get one here. You also have to create a procedure which inserts random data into our testing table. The procedure randomizer() was taken from sixarm.com/about/mysql-create-random-data-text-strings.html, however you don’t need to download it as it’s all mentioned and customised below.
The test itself
Let’s execute steps below in order to find out whether speed of INSERT, DELETE and UPDATE differs.
#login to mysql console and enter password when prompted
mysql -u root -p
--now connect to the database test
use test;
--now create our testing table using InnoDB engine
CREATE TABLE IF NOT EXISTS `foos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--now create a procedure which will insert random data into our table called foos
delimiter $$
CREATE PROCEDURE `randomizer`()
begin
declare i int Default 0 ;
declare random char(20) ;
myloop: loop
set random=conv(floor(rand() * 99999999999999), 20, 36) ;
insert into `foos` (`id`, `name`) VALUES ('',random) ;
set i=i+1;
if i=10999 then
leave myloop;
end if;
end loop myloop;
end $$
delimiter ;
--now call the procedure
call randomizer();
call randomizer();
--in my virtualbox machine I got following:
--Query OK, 1 row affected, 1 warning (53.69 sec)
--Query OK, 1 row affected, 1 warning (42.66 sec)
--now let’s alter table and use MyISAM as its engine
ALTER table foos engine = MyISAM;
--you should see something like following:
--Query OK, 21998 rows affected (0.53 sec)
--Records: 21998 Duplicates: 0 Warnings: 0
--as you can see, I’ve called randomizer twice, let’s call it again with MyISAM table
call randomizer();
--output is as below:
--Query OK, 1 row affected, 1 warning (3.31 sec)
Conclusion of INSERT performance and test of SELECT statements
INSERT statement is much faster with MyISAM tables than with InnoDB tables. However some tables aren’t designed for nonstop insertion of data. Let’s now test the SELECT statement. We will need a procedure that reads data from table foos and we’ll just call it.
--issue the command below in order to create our new procedure
delimiter $$
CREATE PROCEDURE `randomizer_read`()
begin
declare i int Default 0 ;
myloop: loop
select count(*) from `foos` where name not in (select name from `foos`);
set i=i+1;
if i=10 then
leave myloop;
end if;
end loop myloop;
end $$
delimiter ;
--now alter engine to innodb
alter table foos engine = InnoDB;
--and call the procedure
call randomizer_read();
--output with Innodb is following:
--Query OK, 0 rows affected (7.97 sec)
--now it’s time to test SELECT statements with MyISAM engine
ALTER table foos engine = MyISAM;
--call the procedure
call randomizer_read();
--in this case we got following:
--Query OK, 0 rows affected (8.35 sec)
--now call the procedure to insert another 10K of rows
call randomizer();
Conclusion of SELECT performance and test of UPDATE statements
SELECT gives basically same results no matter which engine we use. Now let’s test the UPDATE statement. In order to do so, we have to perform cleanup of column name in our table foos. Let’s use another procedure so we can repeat the whole process easily.
--create procedure randomizer_update
delimiter $$
CREATE PROCEDURE `randomizer_update`()
begin
declare i int Default 0 ;
myloop: loop
update foos set name = concat ( name, SUBSTR( UPPER( md5( rand() ) ) , 1 , 3 ) );
set i=i+1;
if i=5 then
leave myloop;
end if;
end loop myloop;
end $$
delimiter ;
--now empty all columns name
UPDATE foos set name = '';
--you should get something like below
--Query OK, 43996 rows affected (0.43 sec)
--Rows matched: 43996 Changed: 43996 Warnings: 0
--now switch to InnoDB;
ALTER table foos engine = InnoDB;
--let’s call the procedure
call randomizer_update();
--we got following result with InnoDB
--Query OK, 43996 rows affected (10.99 sec)
--now empty all columns name again so we won’t get any warning
UPDATE foos set name = '';
--now switch back to MyISAM
alter table foos engine = MyISAM;
--let’s call the procedure
call randomizer_update();
--we got following result with MyISAM
--Query OK, 43996 rows affected (4.58 sec)
Cleanup
If everything is clear, execute following commands in order to cleanup all above.
--drop the table
DROP table foos;
--remove the procedures
DROP procedure randomizer;
DROP procedure randomizer_read;
DROP procedure randomizer_update;
Final conclusion
If you don’t need foreign keys which are controlled by database (foreign keys can be controlled logically by application if needed) and if you need superb performance with large data, then MyISAM is the right path. If your database contains hundreds of rows, then it’s perfectly OK to choose MyISAM, however when there are tens of millions of rows, then MyISAM should definitely be used.
Further reading & help
If you need help with your MySQL database, drop me a line. You’re also welcome to visit other articles devoted to MySQL - they’re listed below.
- Performing regular backups and point-in-time recovery
- Enabling logging of issued SQL statements
- Understanding indexes
- Memory tuning & filesystem tuning & data optimisation
- Moving datafiles between two databases
- Difference between MYISAM and InnoDB engines
- Partitioning
- Enabling remote access to a MySQL database
- How to setup replication
- Rebuilding indexes and checking health of tables