In my latest database design I will be using a BLOB as a column with the UNIQUE key attribute enabled upon it. The information contained in the blob aggregates three items: a 32-bit signed integer whose value has always been 1 but could change in the future, a 32-bit unsigned integer whose value is derived from the size of an external file, and a 128-bit (16 byte) MD5 hash value of the contents of the external file.
The characteristic of the data table is one of massively frequent retrieval of the rowid based on the contents of the BLOB, with relatively infrequent insertions of new data into the table, and extremely rare deletions of old data from the table. Given that SQLite3 implements only B-tree style indexes, I would like to avoid having a lopsided index caused by the leading portions of the index key being mostly the same, if this is even a concern. I could easily put the hash value in front which, having an equal distribution of leading bit patterns for like-sized quantities of unselected values, would allow for greater "selectivity" in the leading portions of the index key. On the Oracle rdbms (versions 5 through present), B-tree indexes benefit from this choice. Does SQLite performance also benefit from it? Example table schema (unchecked code): create table hashy ( id integer not null primary key, rod blob not null unique ); -- ROD-formation choices equivalent to INDEX1 or INDEX2 in the below table. create table hashy2 ( id integer not null primary key, hash16 blob not null, size integer not null, method integer not null, constraint index1 unique ( hash16, size, method), constraint index2 unique ( method, size, hash16) ); Which of index1 or index2 will provide better selectivity for single-row retrievals? (I have no desire to retrieve or sort by file size, for example.) Thanks in advance. --andy
