2007/2/16, Dan Kennedy <[EMAIL PROTECTED]>:
I think with a virtual table all you can do is:
SELECT * FROM vtable WHERE x = 'a' UNION
SELECT * FROM vtable WHERE x = 'b'
Virtual tables cannot supply an index for WHERE clauses of the form
"x IN ('a', 'b')" or "x = 'a' OR x = 'b'" the way normal tables
can.
Thanks! I was actually hoping the optimizer would do the rewrite for
me. This solution is fine though.
Having a more powerful sqlite3_index_info would help for speed in some cases:
SELECT * FROM vtable WHERE x = 'a' AND (y='b' OR y='c')
would become
SELECT * FROM vtable WHERE x = 'a' AND y='b' UNION
SELECT * FROM vtable WHERE x = 'a' AND y='c'
The difference is probably not large in most cases.
Cheers,
Jos
Dan.
On Fri, 2007-02-16 at 00:31 +0100, Jos van den Oever wrote:
> Hi All,
>
> I'm playing with virtual tables and found that when i do
> SELECT * FROM vtable WHERE x = 'a' OR x = 'b'
> xBestIndex is called without constraints. Is there a way to circumvent
> this? I dont want sqlite to traverse all the rows just because of the
> OR statement.
> The version I'm using is 3.3.13.
> Using
> info->estimatedCost = 100000;
> does not trigger additional calls to xBestIndex.
>
> Cheers,
> Jos
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
