[sqlite] query performance help

Hey everyone,   currently I have some temporary table which I populate this way (recursive triggers on)   CREATE TEMPORARY TABLE IF NOT EXISTS NestedEntriesResult_ID (IDX INTEGER NOT NULL, DBORDER INTEGER PRIMARY KEY NOT NULL);   CREATE TEMPORARY TRIGGER IF NOT EXISTS NestedEntriesPopulate_ID

[sqlite] row storage / column order

Hey everyone,   Imagine tables   CREATE TABLE [Data1] (Index INTEGER PRIMARY KEY NOT NULL, Data BLOB, Description TEXT) vs CREATE TABLE [Data2] (Index INTEGER PRIMARY KEY NOT NULL, Description TEXT, Data BLOB)   essentially those tables are same. however accessing the column Description takes

Re: [sqlite] row storage / column order

_ Od: Simon Slavin Datum: 02.10.2011 14:01 On 2 Oct 2011, at 12:49pm, Mira Suk wrote: CREATE TABLE [Data1] (Index INTEGER PRIMARY KEY NOT NULL, Data BLOB, Description TEXT) vs CREATE TABLE [Data2] (Index INTEGER PRIMARY KEY

[sqlite] LEFT JOIN optimization

EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106)   selectid    order    from    detail 0    0    0    SCAN TABLE ItemsME_Properties (~100 rows)

Re: [sqlite] LEFT JOIN optimization

_ Od: Jim Morris Komu: Datum: 22.09.2011 23:06 Předmět: Re: [sqlite] LEFT JOIN optimization Your where clause WHERE ItemsME.IDR ... is only satisfied if there is an associated ItemsME record so the left outer join is pointless.

Re: [sqlite] c-api document suggestion

On 9/21/2011 21:22 Igor Tandetnik wrote: You can include the NUL terminator, if you want it to actually be stored in the database. Igor Tandetnik Actually you can't - if you do all SQL string functions will not work. to be clear - SELECT TRIM(what ever text column you stored with including

[sqlite] Performance problem LEFT OUTER JOIN and string data from right table

Hey everyone.   new to SQLite so please have patience with me having two tables and doing left outer join   A IDI INTEGER PRIMARY KEY Parent INTEGER INDEX Status INTEGER   B IDR UNIQUE INTEGER FOREIGN KEY IndexME.IDI Points TEXT (at average ~120 character string) (this table is

Re: [sqlite] Performance problem LEFT OUTER JOIN and string datafromright table

  Mira Suk wrote: query written here is a lot simplified (for example Points column is filtered using custom function) however main culprit seems to be LEFT OUTER JOIN as accessing that same column in query which only has B table in it is lightning fast. result of query is just around

Re: [sqlite] Performance problem LEFT OUTER JOIN andstringdatafromright table

Ok then, show the result of prepending EXPLAIN QUERY PLAN to your statement. -- Igor Tandetnik First of all thanks for bearing with me :) functions TZB_MATCHRECURSIVE(int,int) - disabled for this test - always return 1. applies filter recursively TZB_ISCHILD(int) - bitmask check

Re: [sqlite] Performance problem LEFT OUTERJOINandstringdatafromright table

The difference is that #2 mentions only one field from ItemsME, namely IDR. The value of that field comes from the index, the table itself doesn't need to be read at all. It's not even clear why #2 bothers to join with ItemsME at all - it's a no-op. #1 uses more fields from ItemsME, so it