I have a query that joins with a virtual table where the optimizer has a choice of picking between 2 indexes for something, and it picks by far the wrong one.
I'm trying to understand how it all works.
I understand the output of both 'explain' and 'explain query plan' but I can't see from either of those why it will choose one plan over another. Subsequently I don't really know where my estimatedRows and estimatedCost from my virtual table comes into play with the decision so it's hard developing an intuitive feeling for what the correct values are to return.
Is there a way that I can view the cost metrics that goes into each query plan choice to see how it calculates the best index?
On 11/3/17, Deon Brewis <[hidden email]> wrote:
> Is there a way that I can view the cost metrics that goes into each query
> plan choice to see how it calculates the best index?
(1) Get a copy of the canonical source tarball
(2) ./configure --enable-debug; make
(3) ./sqlite3 $yourdatabase
(4) At the "sqlite>" prompt, type ".wheretrace 0xfff".
(5) Enter your query
The 0xfff is a mask of different things to be traced. You have to
refer to the source code to decode the mask. There is no
documentation explaining the output of .wheretrace. The output of
.wheretrace is subject to change.