On 5/15/15, Eric Hill <Eric.Hill at jmp.com> wrote: > > So, in the first case, I do not create an index (which I signify by setting > idxNum to -999), and I set the cost (and, est. rows) to 4581. In the second > case, I create an index (0) and set cost to log10(4581) = 3.66 and est. rows > to 4. Yet, later, whenever xFilter is called for the inventory table, > SQLite passes in idxNum = 999 and nConstraints = 0. The index I dutifully > created is never asked for. In cases where there is a single constraint, > SQLite does ask request the index in the xFilter call, but it seems that for > all the cases where multiple constraints are involved, the index is not > being used. >
Two things: (1) You probably shouldn't be "creating an index" in response to an xBestIndex call. xBestIndex should be thought of as a "what-if" function. It is asking your virtual table what it could do with a query given certain constraints. SQLite makes no guarantees that it will actually ever call your virtual table that way - it is merely exploring possibilities. (2) The query planner looks at many different cost factors and tries to pick the best overall query plan. You've told it that running your virtual table without an index is 1252 times slower than running it with an index. And it takes this into consideration. That SQLite is not choosing to use the virtual table index indicates that some other part or parts of the join would be more than 1252 times slower if the virtual table index were in fact used, and so the overall query plan is faster even without the virtual table index. If these estimates are incorrect, then an obvious work-around is merely to increase the cost of not using the indexing mode on the virtual table. Have it return 10x or 100x the cost (45810 or 458100) when not using an index, and see if that helps. -- D. Richard Hipp drh at sqlite.org
