> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/product-development/server-development/quality/benchmarks-and-long-running-tests/benchmarks/performance-of-memory-tables.md).
# Performance of MEMORY Tables
Between MariaDB 5.5.21 and 5.5.22 some work was done on how the hash index for a [MEMORY](/docs/server/server-usage/storage-engines/memory-storage-engine.md) table is created. This results in better performance when inserting rows into a memory table.
The following benchmark compares MariaDB 5.5.21 and 5.5.25. Compiled with identical settings on the same machine. The operation was loading 50 million rows into a `MEMORY` table with [LOAD DATA INFILE](/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-data-infile.md).
Two different tables were tested: one with an indexed [INT](/docs/server/reference/data-types/numeric-data-types/int.md) column and one with an indexed [CHAR(20)](/docs/server/reference/data-types/string-data-types/char.md) column. The data files were pre-generated and located on a SSD. In order to make the effect visible, the cpu speed was set to minimum (core I5 @ 800Mhz)
#### Result:
| Table Type | MariaDB Version | rows per second | Percent |
| ---------- | --------------- | --------------- | ------- |
| INT | 5.5.21 | 411022 | 100% |
| | 5.5.25 | 510016 | 124% |
| CHAR(20) | 5.5.21 | 259399 | 100% |
| | 5.5.25 | 411535 | 159% |
This is how the benchmark was run:
```sql
MariaDB [test]> tee 5.5.21.txt
MariaDB [test]> set @instance="5.5.21";
MariaDB [test]> source bench.sql
```
The script used to generate the data files:
```perl
#!/usr/bin/perl -w
$ROWS=50*1024*1024;
open F, ">/tmp/hash1.txt" or die;
for ($i=0; $i<$ROWS; $i++) {
printf F "%d\n", int(rand($ROWS));
}
close F or die;
open F, ">/tmp/hash2.txt" or die;
for ($i=0; $i<$ROWS; $i++) {
$s="";
for (1..20) { $s .= chr(ord('a')+int(rand(26))); }
print F $s, "\n";
}
close F or die;
```
The benchmark SQL script `bench.sql`:
```sql
use test;
-- need big heap tables
set max_heap_table_size=4*1024*1024*1024;
-- table to hold test results
create table if not exists results (
id serial,
operation char(32),
opsize bigint unsigned,
started datetime,
ended datetime,
instance char(20)
);
-- dummy run with second data file
drop table if exists t1;
create table t1 (c1 char(20), index (c1)) engine memory;
load data infile "/tmp/hash2.txt" into table t1;
drop table t1;
-- do total of 5 runs for each table
-- run #1
create table t1 (c1 int, index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash1.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into INT table", @rows, @t1, @t2, @instance);
drop table t1;
create table t1 (c1 char(20), index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash2.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into CHAR(20) table", @rows, @t1, @t2, @instance);
drop table t1;
-- run #2
create table t1 (c1 int, index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash1.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into INT table", @rows, @t1, @t2, @instance);
drop table t1;
create table t1 (c1 char(20), index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash2.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into CHAR(20) table", @rows, @t1, @t2, @instance);
drop table t1;
-- run #3
create table t1 (c1 int, index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash1.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into INT table", @rows, @t1, @t2, @instance);
drop table t1;
create table t1 (c1 char(20), index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash2.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into CHAR(20) table", @rows, @t1, @t2, @instance);
drop table t1;
-- run #4
create table t1 (c1 int, index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash1.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into INT table", @rows, @t1, @t2, @instance);
drop table t1;
create table t1 (c1 char(20), index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash2.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into CHAR(20) table", @rows, @t1, @t2, @instance);
drop table t1;
-- run #5
create table t1 (c1 int, index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash1.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into INT table", @rows, @t1, @t2, @instance);
show table status like 't1';
drop table t1;
create table t1 (c1 char(20), index (c1)) engine memory;
select @t1:=now();
load data infile "/tmp/hash2.txt" into table t1;
select @t2:=now();
select @rows:=count(*) from t1;
insert into results (operation, opsize, started, ended, instance)
values ("load into CHAR(20) table", @rows, @t1, @t2, @instance);
show table status like 't1';
drop table t1;
-- list all results
select operation, instance, unix_timestamp(ended)-unix_timestamp(started) as duration,
opsize/(unix_timestamp(ended)-unix_timestamp(started)) as ops_per_sec
from results order by operation, instance, started;
-- list average results
select operation, instance, avg(opsize/(unix_timestamp(ended)-unix_timestamp(started))) as avg_ops_per_sec
from results group by operation, instance;
```
*This page is licensed: CC BY-SA / Gnu FDL*
{% @marketo/form formId="4316" %}
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://mariadb.com/docs/server/reference/product-development/server-development/quality/benchmarks-and-long-running-tests/benchmarks/performance-of-memory-tables.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.