MySQL MyISAM engine and Heap engine performance test (1)

Source: Internet
Author: User
[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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.