On 7/11/07, Steve Krulewitz <[EMAIL PROTECTED]> wrote:
I was wondering if there is much to be gained by storing these 128 bit values in binary rather than as strings. We estimate roughly 20 properties per track, so in a moderate sized database you'd have 10k rows in the tracks table and 200k rows in the properties table. We construct a lot of queries that involve joining the properties table to the tracks table multiple times. Since the integer type is limited to 64 bits, we would have to be creative when storing 128 bit values. Some possibilities: - Use a blob column, but I wonder how efficient indexes and joins are on this column type?
My experience says that blobs are compared with memcmp(), and that indexes based on them appear to be efficient. *MY* blobs that are treated as unique keys are uniformly 20-byte structures, and are bound to parameters in my code using sqlite::execution::bind_blob_reference, which avoids copying the blobs. (the routine is a shim for sqlite3_bind_blob(...,SQLITE_STATIC). I also make sure that my blobs stay "in scope" for the duration of the execution context. andy
