?

Log in

No account? Create an account

Heap Tables Supporting Blobs

« previous entry | next entry »
Aug. 25th, 2009 | 12:49 am

The Example:

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.

Link | Leave a comment | Share

Comments {0}