DROP PROCEDURE IF EXISTS benchins;
DELIMITER go
CREATE PROCEDURE benchins( cengine CHAR(32), isize INT )
DETERMINISTIC
BEGIN
  DECLARE ctr INT DEFAULT 0;
  CREATE TABLE IF NOT EXISTS `bencheng` (
    `ts` timestamp DEFAULT current_timestamp, 
    `engine` varchar(16) DEFAULT NULL,
    `job` varchar(16) DEFAULT NULL,
    `size` int DEFAULT NULL,
    `secs` decimal(8,2) DEFAULT NULL,
    PRIMARY KEY(ts,engine,job)
  );
  DROP TABLE IF EXISTS ipautoinc;
  select concat( "BENCHINS engine:", cengine, " rows:", isize ) as JOB;
  IF cengine = 'myisam' THEN
    CREATE TABLE ipautoinc (
      id INT PRIMARY KEY AUTO_INCREMENT,
      src INT NOT NULL,
      dest INT NOT NULL,
      typ SMALLINT NOT NULL
    ) engine=myisam;
  ELSEIF cengine = 'innodb' THEN
    CREATE TABLE ipautoinc (
      id INT PRIMARY KEY AUTO_INCREMENT,
      src INT NOT NULL,
      dest INT NOT NULL,
      typ SMALLINT NOT NULL
    ) engine=innodb;
  ELSE
    SET @sql = CONCAT("CREATE TABLE ipautoinc(", 
                      "id INT PRIMARY KEY AUTO_INCREMENT,",
                      "src INT NOT NULL,",
                      "dest INT NOT NULL,",
                      "typ SMALLINT NOT NULL",
                      ") engine=",cengine);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
  END IF;
  set @t0=now();
  REPEAT
    INSERT INTO ipautoinc VALUES ( NULL, ROUND(1000000000*RAND(),0), ROUND(1000000000*RAND(),0), ctr % 10);
    SET ctr = ctr + 1;
  UNTIL ctr = isize END REPEAT;
  set @t1=now(6);
  select 'insert' as job, cengine as engine, isize as 'rows',
    round( timestampdiff( microsecond,@t0,@t1)/1000000,2) as time;    
  insert into bencheng 
    set ts=now(), engine=cengine ,job='insert', size=isize,
    secs=round( timestampdiff( microsecond,@t0,@t1)/1000000,2);  
END;
go 
DELIMITER ;
truncate bencheng;
call benchins('myisam',1000);
call benchins('innodb',1000);
call benchins('myisam',5000);
call benchins('innodb',5000);
call benchins('myisam',10000);
call benchins('innodb',10000);
call benchins('myisam',20000);
call benchins('innodb',20000);
select @@key_buffer_size, @@innodb_buffer_pool_size;
SELECT                               
    engine,
    SUM(IF(size=1000,secs,0)) As '1K',
    SUM(IF(size=5000,secs,0)) As '5K',
    SUM(IF(size=10000,secs,0)) As '10K',
    SUM(IF(size=20000,secs,0)) As '20K'
  FROM bencheng
  GROUP BY engine
  ORDER BY engine;

Illustrative results

• 5.6 on a small Windows machine with MySQL installed on a SSD, key)buffer_size=8MB, innodb_buffer_pool_size=400MB
• 5.7 on a small Ubuntu 18.04 system with key_buffer_size=16M,innodb_buffer_pool_size=2G
• 8.0 on a Windows machine with key_buffer_size=8MB, innodb_buffer_pool_size=2GB
• 8.0 on a Ubuntu virtual machine with key_buffer_size=8MB, innodb_buffer_pool_size=68MB

+---------+--------+------+-------+-------+-------+
| version | engine | 1K   | 5K    | 10K   | 20K   |
+---------+--------+------+-------+-------+-------+
| 5.6 win | innodb | 4.69 | 22.59 | 43.67 | 87.75 |
| 5.6 win | myisam | 0.23 |  1.09 |  1.41 |  1.50 |
| 5.7 nix | innodb | 2.50 | 11.91 | 22.24 | 48.24 |
| 5.7 nix | myisam | 0.31 |  0.00 |  1.32 |  1.03 |
| 8.0 win | innodb | 3.67 | 17.17 | 29.06 | 93.18 |
| 8.0 win | myisam | 1.44 |  6.07 | 11.31 | 24.47 |
| 8.0 nix | innodb | 2.11 |  7.81 | 15.27 | 28.54 |
| 8.0 nix | myisam | 1.65 |  4.67 |  9.94 | 18.57 |
+---------+--------+------+-------+-------+-------+

A strange pattern emerges ...

• MyISAM vs version: MyISAM Inserts are eight to 20 times slower with 8.0 than with 5.6 & 5.7.
• Linux vs Windows: InnoDB Inserts are about two times faster under Linux than under Windows
• MyISAM vs InnoDB vs version: Because of the above two effects, before 8.0 MyISAM inserts are 20-60 times faster than InnoDB inserts, but in 8.0 this difference has largely been obliterated

Last updated 17 Feb 2025