[sqlite] Contentless FTS4 Tables

Hi! I have made a Contentless FTS4 Table like: "CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR` varchar;" And managed to insert some data into it. I have 2 questions: 1.) How to get the proper COUNT on the table ? The only query seems to work is: "SELECT COUN

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

Hello! Sorry if this report shows up as a duplicate but didn?t figure out jet why some of my emails not showing up or getting rejected. This is my first "bug" report here so please bear with me for blunders. Using: ?sqlite-amalgamation-3081002.zip? Build as: ?cl sqlite3.c -O2 -D

[sqlite] Contentless FTS4 Tables

Hello Dan, sorry it is just like: CREATE VIRTUAL TABLE if not exists OCR USING fts4(content="", `FullOCR`); & CREATE TABLE `ART` ( `ID`integer NOT NULL, `Kundennummer`integer, `Rechnungsnummer`varchar, `Rechnungsdatum`datetime, `PDF`varchar, PRIMARY K

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

Hi Clemens, I really appreciate your input but non of your origination have anything to do with the problem at hand. I just want to make sure this thread not get deflected. The problem is ?performance?. 34157ms, 47924ms vs. 103ms >>SQLite ignores pretty much anything except the column names Al

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

Thank You Clemens, >>INSERT INTO OCR(docid, FullOCR) VALUES (42, 'xxx'); I understand now what you are trying to tell me I have misunderstood the documentation. - or lack of it - Do you have some explanation of the terrible performace of "NOT EXIST" & "LEFT JOIN" versus "NOT IN" as well ? Regar

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

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 "CR

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

Thanks Simon, >>Only the content you need to search quickly needs to be in an FTS table. I have only one column with the full text OCR extracted from PDF files and it is what need to be searched. I can't go lower than 1 column its obvious. The "MATCH" gives me back the correct docid which corre

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

Allright Simon, finally I have figured it out. SELECT ART.* FROM ART JOIN (SELECT docid FROM OCR WHERE FullText MATCH 'mailing') AS ftstable WHERE ART.ID = docid; Pity that the documentation is so minimal that it causes more confusion as it helps due to by reading it you have the false impress

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

>>SELECT * FROM ART WHERE ID IN (SELECT docid FROM OCR WHERE FullText MATCH >>'mailing') Thnak You Clemens that's excellent. KR, Marta -Original Message- From: Clemens Ladisch Sent: Monday, May 25, 2015 9:59 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] NOT EXIST