_
> 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 TE
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
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
AF
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
_
> 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
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)
0
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 nee
>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
TZB_MATCHD
> 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
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 primarily
10 matches
Mail list logo