Thanks for your reply. I went as high as using (number of rows)^4, resulting in a cost of ~440 trillion for the unindexed case, along with setting the cost to 1 for the indexed case, and it still won't use my index.
I'd like to step out of my xBestFilter implementation into SQLite code to see if I can tell what is going on there, but when I step out, the call stack knows where I'm supposed to be, but the debugger does not find the right line in sqlite3.c. I have built sqlite3.c simply by adding the amalgamation to my Microsoft Visual Studio 2013 C++ project. Is there something I can do to make the debugger work? I will postpone index creation until the call to xFilter, I reckon, once I work out these other issues. Thanks for the tip! Eric -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Friday, May 15, 2015 12:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes? 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
