5 15:55
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler.
The example query from that thread was:
SELECT t1.rental_date, t1.inventory_id, t8.film_id,
e] Virtual Table query - why isn't SQLite using my indexes?
Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler.
The example query from that thread was:
SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title,
t3."name" AS Category, t4.cust
Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler.
The example query from that thread was:
SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title,
t3."name" AS Category, t4.customer_id, t4.store_id, t4.email,
t5.length
FROM rental
Sorry, scratch that. T2 was not a virtual table when I ran this query. My
bad. Told you I was a noob.
Eric
-Original Message-
From: Eric Hill
Sent: Wednesday, May 20, 2015 12:32 PM
To: 'General Discussion of SQLite Database'
Subject: RE: [sqlite] Virtual Table query - why isn't
Eric had said:
>> But then what about a query like this:
>>
>>SELECT * FROM T1
>>LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = T1.b ) AND
>> ( T2.c = T1.c );
>>
>> xBestIndex will get called here for T1 with 3 constraints, c, b, and a, in
>> that order.
To which
-Urspr?ngliche Nachricht-
Von: Eric Hill [mailto:Eric.Hill at jmp.com]
Gesendet: Dienstag, 19. Mai 2015 22:44
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Hey, Gunter,
...
But then what about a query like
On Tue, 19 May 2015 20:44:17 +
Eric Hill wrote:
> But then what about a query like this:
>
> SELECT * FROM T1
> LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b =
> T1.b ) AND ( T2.c = T1.c );
>
> xBestIndex will get called here for T1 with 3 constraints, c, b, and
> a,
sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
Sent: Tuesday, May 19, 2015 3:20 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Eric
Working backw
to determine which indexes
to maintain beforehand.
-Urspr?ngliche Nachricht-
Von: Eric Hill [mailto:Eric.Hill at jmp.com]
Gesendet: Montag, 18. Mai 2015 22:22
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Per Richard's
Per Richard's request, I have produced the WhereTrace for the query that uses
virtual tables and the same query that is not using virtual tables. That
output is at the end of this message. Ultimately, I think my question is, how
should I respond to xBestIndex such that SQLite will perform the
Eric: Can you please recompile the SQLite shell (sqlite3.exe) with
-DSQLITE_DEBUG and -DSQLITE_ENABLE_WHERETRACE and then run your query
after first running
.wheretrace 0xff
And then send us the output? (In order for this to work, you'll have
to build your virtual table implementation as a
c.Hill at jmp.com]
Gesendet: Freitag, 15. Mai 2015 18:03
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Hey,
Let me say up front that I'm sure this is my fault. I have SQLite version
3.8.9.
I am using virtual tables, and I am
Thanks, Dominique, for the tip on multifile amalgamation. After a few internet
searches and the acquisition of gawk.exe and tclsh85.exe, I was able to build
the multi-file amalgamation for 3.8.10. Tomorrow, I will see if I can tell why
SQLite is choosing table scans over using my indexes. If
On Fri, 15 May 2015 18:34:33 +
Eric Hill wrote:
> 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
On Fri, May 15, 2015 at 8:34 PM, Eric Hill wrote:
> Is there something I can do to make the debugger work?
>
See this thread:
http://sqlite.1065341.n5.nabble.com/Windows-Specific-2-c-files-Amalgamation-td67626.html
Basically, use a multi-file amalgamation, that uses files with fewer than
64K
...@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 wrote:
>
> So, in the first case, I do not create an index (which I signify by
>
Hey,
Let me say up front that I'm sure this is my fault. I have SQLite version
3.8.9.
I am using virtual tables, and I am trying to get xBestIndex and xFilter doing
the right things so that I get optimal queries. Currently, I am working on the
following query:
SELECT
On 5/15/15, Eric Hill 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
18 matches
Mail list logo
