Hey all -- In the application I am working on (Songbird), we have a simple two table schema representing the tracks in your music collection and the properties on those tracks. The keys used are all UUIDs (128 bit number) which we are currently storing in hex string form in a text column, so they literally appear in the database as "ee89b823-e709-40c5-bed7-dcb0b2b791a8". We do lots of lookups and joins on these keys.
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 pair of integer columns - Continue to use a text column and shove binary data into it and hope for the best - Use a blob column, but I wonder how efficient indexes and joins are on this column type? - Create another table in the schema that maps integers to UUIDs, and use these integers in the rest of the schema rather than the UUIDs. This would add an additional join every time we want to do a query based on UUID. Is it worth the trouble to make a change here? The full schema can be found here: http://publicsvn.songbirdnest.com/browser/trunk/components/library/localdatabase/content/schema.sql The tracks table is called "media_items" and the properties table is called "resource_properties". cheers, -steve ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
