On Sun, Dec 19, 2010 at 04:43:05AM -0800, Roger Binns scratched on the wall: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 12/19/2010 04:21 AM, Sören Brunk wrote: > > I'm wondering if there is any way to pass that additional information to > > xFilter besides encoding it into idxNum/idxStr somehow. > > That is the mechanism to use. Remember that internally SQLite uses only > one index, hence xBestIndex to find "the" index and xFilter not needing > much extra information to work with the selected index.
The "one-index-per-table-per-query" rule of thumb is a limitation on the B-Tree storage system and standard indexes, not SQL or SQLite inherently. Because virtual tables use their own storage systems (by layering together multiple standard tables, or using some external data source), the rule does not apply unless the VT chooses to make it apply in its xBestIndex() function. For example, the whole point of the R-Tree virtual table is that it can use multiple internal "indexes" to quickly find what it is looking for. The name of the xBestIndex() function is also a bit confusing. It is really about processing query constraints, not indexes. It just happens that, within SQL, the standard tool to efficiently process constraints is to use indexes. But it is really about constraints, and if you're dealing with a VT that might have multiple shadow tables (possibly with multiple indexes) or is using an external data store, there is really no say about how many "indexes" may or may not be used to process a series of constraints within a single query upon a VT. What it all boils down to is using constraint information to short-cut as much data processing as possible, which is more or less saying that every performance-related VT needs to implement its own custom optimizer. That can get pretty complex, but that's why many VTs tend to be target a very specific style of data storage and query, and why the best known VTs are products like FTS and R-Tree. -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
