On Fri, Aug 20, 2010 at 7:39 AM, Jim Wilcoxson <pri...@gmail.com> wrote: > ... > The best I could come up with is a separate table. The problem is, > indexing the SHA1 normally means there is a copy in the row and a copy > in the index. Using a separate table, which still has to be indexed, > means there is a copy in the row of the main table, a copy in the > separate table, and a copy in the separate table's index. >
You might want to index the "sha" column on the primary table, then the secondary table can just be indexedblocks(blockid INTEGER PRIMARY KEY). No wasted space that way, but will require more I/Os to JOIN the tables. > > I guess one way to do this using the SQL partial index feature being > discussed would be to have an extra column in the row called > "isindexed", setting that to 1 or 0 depending on whether the SHA > should be included in the index, and using create index ... where > isindexed=1. Then on the queries, say "select blockid from blocks > where isindexed=1 and sha=X". And any query that didn't have > isindexed=1 wouldn't be able to use the index at all. > > Is that how it would work? Yes, that's exactly how it works. Partial indexes are best used when: - You only need to search a specific subset of your rows. "isindexed" is a good candidate for this. - You have no other indexes that can already narrow down searches efficiently. - You have existing queries that use rows from both inside and outside of the partial index. If you don't, then there's no reason to group them together and you might as well put the subset in a separate table if it's not too inconvenient. - The column is used outside of the index, or the storage used by it is acceptable. Ie. if 99% of your rows have "isindexed=1", you've got a lot of redundant columns taking up space and might investigate JOINing with another smaller table instead. -- Cory Nelson http://int64.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
