On Fri, Aug 20, 2010 at 06:52:40AM -0700, Cory Nelson scratched on the wall: > On Thu, Aug 19, 2010 at 1:30 PM, Eric Smith <eas....@gmail.com> wrote: > > Afaict sqlite doesn't support indices on subsets of rows in a table, ?? > > la http://en.wikipedia.org/wiki/Partial_index -- right? > > > > Any plans to implement that? > > +1 for this feature request. They've got a very specific and fairly > rare use case, but when opportunity strikes partial indexes are much > more convenient, straightforward, and efficient than the alternative.
This strikes me as more trouble than it is worth. You admit it has a very specific and rare use case, yet implementing it is going to add complexity and size to the query processor and optimizer. This is not a "low cost" feature. Any time a partial index comes into play, the optimizer needs to determine if the index can be used or not by verifying that the query domain is a subset of the index domain, based off the conditions on the query and on the partial index. This may or may not involve some significant logic (or it will tend to be rather dumb). It requires noticeable amounts of development and testing time-- especially testing, as it introduces the possibility of returning a looks-correct-but-is-wrong answer. And, because you're talking about modifications to a very central part of the database engine, adding complexity adds upkeep costs from here on out. It is also likely you would need to bump the file format, since you now need the ability to attach conditions to an index and flag it as partial and unusable for the general case. I have no doubt that it is useful for those cases where it applies, but that seems like a high costs for such a narrow gain. > > Are there any known hacks to implement something similar? > > There's not really any good solution. You can create a separate table > with the subset in it, that's pretty much it. Right. Create a shadow table that has the fields you want to partially index. Map the ROWID values one-to-one. Index the shadow table (or not, depending on your needs). Put triggers on the main table to keep the shadow table up to date on INSERT/UPDATE/DELETEs. If you index the shadow table, you now have a partial index. You have to make the call with each query about using the partial index or not, and you have to manually add an extra JOIN into your query, but for most of the situations described in the tread so far, that doesn't seem that difficult. Performance-wise, queries should be the same as a native partial index, since the ROWID value can be pulled directly from the index and mapped back to the main table, exactly like a native index. > But that has some overhead too, and can complicate your queries. The complexity is there no matter what. The difference is if the person writing the query sees it or not. My personal opinion is that this is a unique enough feature, and workarounds exist (even if they aren't exactly pretty), that it does not justify the long-term testing and upkeep costs. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
