Re: [sqlite] Optimization opportunity

Am Fri, 14 Apr 2017 15:14:12 -0400 schrieb Richard Hipp: > But I've spent Good Friday working around it. A thousand thanks! :-) > Please try using the tip of the left-join-view branch > (https://www.sqlite.org/src/timeline?c=left-join-view) and let me know > if that version works better for

Re: [sqlite] Optimization opportunity

On 4/14/17, Wolfgang Enzinger wrote: > > Thank you Richard. I have to admit that it took me quite a while and also > reading the comment for check-in [1838a59c] several times to really > understand your explanation. Duh, that's tricky indeed! > But I've spent Good Friday

Re: [sqlite] Optimization opportunity

Am Fri, 14 Apr 2017 10:59:25 -0400 schrieb Richard Hipp: > Performing this rewrite of a view into a simple LEFT JOIN is trickier > than it seems at first glance. The rewrite works for the example you > provide. But subtle changes to the view can make the rewrite invalid. > For example: > >

Re: [sqlite] Optimization opportunity

On 4/14/17, Wolfgang Enzinger wrote: > > CREATE VIEW z AS SELECT > fk, > (flags&1) AS odd, > (flags&2)>>1 AS even, > (flags&4)>>2 AS prime > FROM y; > > Now using the VIEW z in a JOIN results in a full table scan on TABLE y > despite a WHERE clause and an

Re: [sqlite] Optimization opportunity

Hello ! Maybe this problem would be the reason of getting bad query plans when joining views too. Cheers ! On 14/04/17 08:03, Wolfgang Enzinger wrote: Hello, given the following: CREATE TABLE x( pk INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE y(

[sqlite] Optimization opportunity

Hello, given the following: CREATE TABLE x( pk INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE y( fk INTEGER REFERENCES x(pk), flags INTEGER ); CREATE INDEX yy ON y(fk); CREATE VIEW z AS SELECT fk, (flags&1) AS odd, (flags&2)>>1 AS even,

[sqlite] Optimization Opportunity?

Op 13 mrt 2015, om 00:03 heeft Wolfgang Enzinger het volgende geschreven: > Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma: > >> Actually query one appears slightly faster, >> Searching the PK index is faster as that is always a COVERING index. > > I was under the impression that the

[sqlite] Optimization Opportunity?

Am Sun, 8 Mar 2015 14:06:51 +0100 schrieb E.Pasma: > Actually query one appears slightly faster, > Searching the PK index is faster as that is always a COVERING index. I was under the impression that the opposite is true, but I wasn't sure about that. > From the secunsary indexes only a part

[sqlite] Optimization Opportunity?

>> Actually query one appears slightly faster, >> Searching the PK index is faster as that is always a COVERING index. > >I was under the impression that the opposite is true, but I wasn't sure >about that. The primary key is only a covering index if you are only accessing fields comprising the

[sqlite] Optimization Opportunity?

f: [sqlite] Optimization Opportunity? Hi dev team, not sure if this is actually a useful hint, but ... CREATE TABLE a(a1 INTEGER PRIMARY KEY); INSERT INTO a VALUES (1),(2),(3); CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY); INSERT INTO b VALUES (1,11),(2,22),(3,33); CREATE UN

[sqlite] Optimization Opportunity?

Op 7 mrt 2015, om 19:24 heeft Wolfgang Enzinger het volgende geschreven: > Hi dev team, > > not sure if this is actually a useful hint, but ... > > CREATE TABLE a(a1 INTEGER PRIMARY KEY); > INSERT INTO a VALUES (1),(2),(3); > CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY); >

[sqlite] Optimization Opportunity?

Hi dev team, not sure if this is actually a useful hint, but ... CREATE TABLE a(a1 INTEGER PRIMARY KEY); INSERT INTO a VALUES (1),(2),(3); CREATE TABLE b(a1 INTEGER REFERENCES a(a1),b1 INTEGER PRIMARY KEY); INSERT INTO b VALUES (1,11),(2,22),(3,33); CREATE UNIQUE INDEX b_ui ON b(a1,b1); CREATE