Benchmarks

I've often wondered how much it matters to store tables in memory, rather than just on disk.

It turns out to matter quite a bit.

In one bookworm with 6,000,000 documents, I created a secondary lookup table of 300,000 elements for one variable.

I'm going to natural join three tables to it

1. genderheap: an on-disk table with a B-Tree index onto the 3-byte bookid.
2. genderheap2: an in-memory table with an index onto the 3-byte bookid.
3. genderheap2: an in-memory table with a PRIMARY KEY of the 3-byte bookid.

As you can see, it's about 5x faster with the in-memory PRIMARY KEY based merge. This is a huge deal, because there are frequently two of these merges per query.

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM fastcat NATURAL JOIN genderheap;
+----------+
| COUNT(*) |
+----------+
| 6060912 |
+----------+
1 row in set (6.11 sec)

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM fastcat NATURAL JOIN genderheap2;
+----------+
| COUNT(*) |
+----------+
| 6060912 |
+----------+
1 row in set (1.69 sec)

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM fastcat NATURAL JOIN genderheap3;
+----------+
| COUNT(*) |
+----------+
| 6060912 |
+----------+
1 row in set (1.20 sec)