Even though SQLite is small enough to be embedded in a phone, there are quite a few questions here about large databases and performance optimization. My question fits in with the latter. Have the SQLite architects considered adding hash key as an option to the b-tree?
One of the main tables in my current (lexicographic) database in SQLite would be a prime candidate for hash keys. It contains many rows, is never sorted, subsets are almost never extracted from it (i.e.groups of rows are not extracted using some common attribute) and almost every query fetches thousands of records from this table by their PK. In such a scenario, hash-keys can be faster than b-trees. I don't mean to suggest that SQLite is slow, just the contrary. I'm very pleased with its performance and would call it "blistering". But I am looking forward to a much larger database. The first database I ever worked with was optimized for OLTP and used sparse tables with hashed primary keys. The algorithm was right-weighted and sequential numbers worked well as PKs. Hash keys delivered excellent performance when inserting or retrieving records even if the table contained millions of rows. Contention was minimized. No need for table locks, row locks sufficed. New records were interspersed not appended to a caboose, so in a busy data-entry scenario processes weren't wanting to claim the same block. And there was no b-tree structure to keep balanced. The tradeoff was that sorting and selecting *groups* of records based on a column value was relatively slow because the tables were sparse and the physical order of records was totally driven by the hash value of the key: you had to do a full table scan to select a subset. B-trees were later added to address those issues. Regards Tim Romano _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
