import benchmarks for mysql

ActiveRecord::Base has been given an import method which supports optimized inserts through the methods like multi-value insert statements. Below are the benchmarks for several sets of data across the two most common MySQL storage engines.

This functionality can be found in my ActiveRecord Extensions package which can be found here. The current version as of the time of this benchmark was 0.0.3.

This benchmark test compares the speed of two identical tables (besides the storage engine in use) with the following three columns:

• id int(11) unsigned not null auto_increment
• my_name varchar(20) not null
• description varcharr(255) default ’’

It utilizes the following three methods:

• ActiveRecord::Base.create – included with ActiveRecord
• ActiveRecord::Base.import – which uses model validation. This is included with ActiveRecord Extensions
• ActiveRecord::Base.import – which doesn’t use model validation. This is included with ActiveRecord Extensions

The titles of each table are identified with the black background color. They read something similar to ModelName.method_name (number_of_inserts) Description.

The times of each table are represented in seconds. They are rounded to the nearest thousandth for display purposes.

The speedup you see in columns two and three enxt to the time in seconds is how much faster this method is when compared to the create method.

The model name can be used to determine the storage engine in use. The method name can be used to determine what method was called on the model. The number_of_inserts can be used to determine how many inserts we’re talking about. The additional description can be used to determine when validation on the model was performed or not.

TestMyISAM.create (10)	TestMyISAM.import (10) With Validations	TestMyISAM.import (10) Without Validations
0.032	0.005 (6x speedup)	0.003 (12x speedup)

TestInnoDb.create (10)	TestInnoDb.import (10) With Validations	TestInnoDb.import (10) Without Validations
0.044	0.006 (7x speedup)	0.004 (12x speedup)

TestMyISAM.create (100)	TestMyISAM.import (100) With Validations	TestMyISAM.import (100) Without Validations
0.336	0.044 (8x speedup)	0.009 (36x speedup)

TestInnoDb.create (100)	TestInnoDb.import (100) With Validations	TestInnoDb.import (100) Without Validations
0.366	0.112 (3x speedup)	0.011 (35x speedup)

TestMyISAM.create (1000)	TestMyISAM.import (1000) With Validations	TestMyISAM.import (1000) Without Validations
3.428	0.556 (6x speedup)	0.072 (47x speedup)

TestInnoDb.create (1000)	TestInnoDb.import (1000) With Validations	TestInnoDb.import (1000) Without Validations
4.224	0.428 (10x speedup)	0.2 (21x speedup)

TestMyISAM.create (10000)	TestMyISAM.import (10000) With Validations	TestMyISAM.import (10000) Without Validations
35.146	3.974 (9x speedup)	0.894 (39x speedup)

TestInnoDb.create (10000)	TestInnoDb.import (10000) With Validations	TestInnoDb.import (10000) Without Validations
41.951	4.003 (10x speedup)	1.105 (38x speedup)

As you do a larger number of mass or batch inserts utilizing the import functionality from ActiveRecord Extensions drastically reduces the time required to process those records especially when you aren’t using validations.

If these numbers look appealing to you please read the ActiveRecord Extensions page to determine if this functionality will meet your needs.