[From heiyeluren blog] test environment CPU: Memory: 1 GBDisk: 73 GBSCSIOS: FreeBSD4.11PHP: PHP5.2.1MySQL: MySQL4.1.23b preliminary work my. login create usetest table; ---- Storeengineheap -- CREATETABLE
[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 table use test; -- Store engine heap -- CREATE TABLE
[From heiyeluren's 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 = 128M |
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=InnoDB 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 |
Where condition, 10000 times, each time starting from 1000, take 1000 records
Heap engine select 10000 times, 1000 record used time: 124.28988695145 MyISAM engine select 10000 times, 1000 record used time: 139.82107305527 |
The where condition adds like, 10000 times, and starts from 1000 records each time, and takes 1000 records
Heap engine select 10000 times, 1000 record used time: 145.43780493736 MyISAM engine select 10000 times, 1000 record used time: 163.56296992302 |
The where condition adds an index, 10000 times, and starts from 1000 records each time. 1000 records are retrieved.
-- Create an index (executed under SQLyob) Alter table tbl_heap add index idx_name (name ); Alter table tbl_isam add index idx_name (name ); Heap engine alter table add index used time: 2.078 MyISAM engine alter table add index used time: 13.516 Heap engine select 10000 times, 1000 record used time: 153.48922395706 MyISAM engine select 10000 times, 1000 record used time: 239.86818814278
|
PS: it is better not to use an inappropriate index.