Am Fri, 14 Apr 2017 15:14:12 -0400 schrieb Richard Hipp:
> 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
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 you.
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 working around it. Pleas
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:
>
> CREA
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 appropriate INDEX:
>
> EXPLAIN Q
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(
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,
(flags&4
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 opposit
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 o
>> 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
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
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);
> IN
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 TA
13 matches
Mail list logo
