[sqlite] NOT EXISTS and LEFT JOIN Performance problem

ShadowMarta at yahoo.de wrote: > ok, understand but I can't see why "NOT IN" would not force a full table scan > but "LEFT JOIN" would ? In a query like "SELECT ... WHERE ID NOT IN (SELECT ...)", the subquery is executed _once_, the results are put into a temporary table/index, and the remaining

[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

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

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

On 25 May 2015, at 2:09pm, ShadowMarta at yahoo.de wrote: > What am I missing here ? My guess is that you are expecting FTS tables to act the same as normal tables. They don't. They're specially created and don't have the same indexing and other behaviour. The questions you're asking make se

[sqlite] Sample SQL code thats beyond me :(

Simon, The issue was not storing 1 or 0 per se, but rather thinking through the logic of what it could be under certain circumstances. Our app provides highly personalised traffic information for Londonders. One aspect of the app is to allow alerts to be sent to people when their app isn?t eve

[sqlite] Sample SQL code thats beyond me :(

On 23 May 2015, at 5:21pm, Rob Willett wrote: > If we can force holiday_mode to be set to either 0 or 1 then the problem goes > away, which comes down to getting the design right. I don't know how your synchronisation works, but you can definitely make sure only 'legal' values are stored in a

[sqlite] [BUG?] BEFORE INSERT trigger has NEW.pk = -1 when NULL is passed for PK

See: https://www.sqlite.org/mark/lang_createtrigger.html?Cautions&The+val*ger.#mark On 5/24/15, Tomash Brechko wrote: > Hello, > > The following code > > -- beg -- > CREATE TEMP TABLE t (pk INTEGER PRIMARY KEY, i); > CREATE TEMP TABLE b (before_pk, i); > CREATE TEMP TABLE a (after_pk, i); > > CR

[sqlite] Extending VFS documentation

Thanks, Jeff. That's an interesting project; redirecting to iostream. I'm sure it'll be useful, though an authoritative source for documentation on the SqLite website itself would be ideal. The methods themselves and their arguments seem easy enough; my main concern is what error codes I may re

[sqlite] Extending VFS documentation

Hi Roger, For experimentation, I definitely agree that using a higher level language is better. What I am after is an authoritative source for exact behavior of the various functions, what different errors they may return and what the ramifications of not implementing various methods are, etc