Hi Clemens,
ok, understand but I can't see why "NOT IN" would not force a full table
scan but "LEFT JOIN" would ?
SELECT ART.ID FROM ART LEFT JOIN OCR ON OCR.ID = ART.ID WHERE OCR.ID IS
NULL;
Is this query on "rowid" or isn't ?
Another thing:
I have converted my table like you proposed to "CREATE VIRTUAL TABLE "OCR"
using fts4("FullText" varchar) and I can insert the rows just fine but can't
make a functioning "JOIN" query.
I am just getting weird results.
"2 Rows returned from: SELECT docid FROM OCR WHERE FullText MATCH 'mailing';
(took 2ms)"
the rows 2 & 4.
But if I put this statement into a "JOIN" I get:
"9 Rows returned from: SELECT * FROM ART INNER JOIN OCR ON ART.ID = (SELECT
docid FROM OCR WHERE FullText MATCH 'mailing'); (took 3ms)"
ID Kundennummer Rechnungsnummer Rechnungsdatum PDF FullText
2 . .
. . .
2 . .
. . .
2 .
2 .
2 .
2 .
2 .
2 .
It should give back only two rows 2 & 4 have no other matching ART.ID =
docid.
And why it is returning "FullText" as well just adds to the mistery.
What am I missing here ?
KR,
Marta
-----Original Message-----
From: Clemens Ladisch
Sent: Sunday, May 24, 2015 10:11 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] NOT EXISTS and LEFT JOIN Performance problem
ShadowMarta at yahoo.de wrote:
> Do you have some explanation of the terrible performace of "NOT EXIST" &
> "LEFT JOIN" versus "NOT IN" as well ?
FTS tables can do two types of queries efficiently:
- lookups by rowid/docid;
- searches with MATCH.
Anything else (such as your "WHERE id = ?") ends up as a full table scan for
each value.
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
