drizzle> create TEMPORARY table mine1 (a int, b blob) ENGINE=HEAP; Query OK, 0 rows affected (0 sec) drizzle> show create table mine1; +-------+-------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------+ | mine1 | CREATE TEMPORARY TABLE `mine1` ( `a` int DEFAULT NULL, `b` blob ) ENGINE=MEMORY | +-------+-------------------------------------------------------------------------------------+ 1 row in set (0 sec) drizzle> insert into mine1 VALUES (1, "sfdssdfsadsfssdf123"); Query OK, 1 row affected (0 sec) drizzle> select * from mine1; +------+---------------------+ | a | b | +------+---------------------+ | 1 | sfdssdfsadsfssdf123 | +------+---------------------+ 1 row in set (0 sec)
This has been in my list for awhile. The Drizzle Heap/Memory engine was built on top of the work done by E-Bay for their custom version. It uses variable length strings instead of fixed length, and therefor ends up using a bit less memory. Inside of Drizzle we don't really have a concept of a "fixed length string". We have strings and if you need one above a certain size you need to declare it a TEXT (the funny thing about this... within a year or so we should be able to throw away this distinction as well). The difference between a "blob" and a "text" is really just collation at this point.
So why does this matter?
This means that in cases where a temporary table needs to be built, we don't automatically require that a table "go to disk" just because it has a blob in it.
Nifty :)
There is still a lot to be done, and we don't know that long term we will keep the Heap engine. It has a lot of warts, and its lack of re-entrance really means that it is a pretty limited design. A lot of work would need to be done to bring it up to par with any of the first class engines. It would be nice to see someone go in a convert it to C++, and even better? Fix it so that it could spill to disk on its own (this would remove the expensive operation of conversion that happens if a Heap table is being used as an internal temporary table and it ran out of disk).
Creating a new Heap engine, or even taking the current one and modernizing it a bit, would make for a fun project.