MySQL MyISAM engine and Heap engine performance test (1) _ MySQL

MySQL MyISAM engine and Heap engine speed performance test (1) [from heiyeluren blog] test environment

CPU: Intel Pentium4 2.66 GHz

Memory: 1 GB

Disk: 73 GB/SCSI

OS: FreeBSD 4.11

PHP: PHP 5.2.1

MySQL: MySQL 4.1.23b

Preliminary work

My. cnf

Max_heap_table_size = 128 M

Create a table

Use test; ---- Store engine heap -- create table 'tbl _ heap '('id' int (11) not null auto_increment, 'name' varchar (32) not null default '', 'email 'varchar (32) not null default '', 'Summary 'varchar (255) default'', KEY 'id' ('id ')) ENGINE = heap default charset = gbk; ---- Store engine myisam -- create table 'tbl _ isam' ('id' int (11) not null auto_increment, 'name' varchar (32) not null default '', 'Email 'varchar (32) not null default'', 'summary' varchar (255) default '', KEY 'id' ('id ')) ENGINE = MyISAM default charset = gbk;

Insert data

Note: Data is inserted into an empty table every time.

Insert 10000 Record

Heap engine insert 10000 record used time: 3.5008587837219 MyISAM engine insert 10000 record used time:

4.5881390571594

50000 Record

Heap engine insert 50000 record used time: 19.895354986191 MyISAM engine insert 50000 record used time: 33.866044998169

100000 Record

Heap engine insert 100000 record used time: 36.200875997543 MyISAM engine insert 100000 record used time: 68.34194111824

200000 Record

Heap engine insert 200000 record used time: 68.00207901001 MyISAM engine insert 200000 record used time: 125.26263713837

Query data

Table shards have 200000 records, and the two tables have the same data.

Select directly, 10000 times, get 100 records each time

Heap engine select 10000 times, 100 record used time: 12.122506141663 MyISAM engine select 10000 times, 100 record used time: 19.512896060944

Select directly, 1000 times, get 10000 records each time

Heap engine select 1000 times, 10000 record used time: 111.54126811028 MyISAM engine select 1000 record used time: 116.79438710213

Add the where condition, 1000 times, and get 10000 records each time

Heap engine select 1000 times, 10000 record used time: 111.52102303505 MyISAM engine select 1000 times, 10000 record used time: 117.68481087685