Nick suggested that I send this on to mobile-firefox-dev. The frontend team was discussing SQLite partial indices this morning. I haven't seen them used in our codebase yet, so here's a brief summary.
SQLite allows you to create an index that is incomplete: that is, an index that doesn't contain an entry for every row in the table. Complete indices are (conceptually, at least) permutations of the whole table, sorted by some column or combination of columns. Rather than walking the unsorted table, you walk the appropriate sorted index on the sorted column, and have less work to do when running the query. Why would you use a partial index? Primarily for the space benefits, but maintaining indices makes INSERTs slower, too. An example! Let's say that you're syncing history. You sometimes query on should_upload, a boolean column. But you only ever care about should_upload=1! There's no point having a complete index on should_upload; most records have been uploaded, so we'd have a huge index file that would be mostly ignored. So we do this: CREATE INDEX index_history_should_upload ON history(should_upload) WHERE should_upload = 1 The resultant index is partial, and will only be able to answer queries like SELECT * FROM history WHERE should_upload = 1 When an item is marked as should_upload = 0 it will be removed from the index. When a downloaded history item is inserted (with should_upload = 0, because it's already synced), it's never inserted into this index at all. More examples and caveats in the SQLite docs: https://www.sqlite.org/partialindex.html -R
_______________________________________________ mobile-firefox-dev mailing list [email protected] https://mail.mozilla.org/listinfo/mobile-firefox-dev
