[sqlite] Query planning with concatened vs single index

2013-01-14 Thread Selen Schabenberger
Hi, Having an index on 3 integer columns (column1, column2, column3), the analyse command generates the stat1 and stat3 tables. I see the following statistics for this index in the sqlite_stat1 table: "4600132 1289 1275 1" When I execute the following SQL query, this index is not used but the

Re: [sqlite] Query planning with concatened vs single index

2013-01-14 Thread Simon Slavin
On 14 Jan 2013, at 2:30pm, Selen Schabenberger wrote: > As far as I know, the index (column1, column2, column3) is more useful than > the index (column1). Why does the analyzer not like the concatened index for > that query? Did I misunderstand the whole idea with the

Re: [sqlite] Query planning with concatened vs single index

2013-01-14 Thread Selen Schabenberger
The results I wrote was after executing the ANALYZE.  But If I drop the stat1 and stat3 tables, the query is faster and I see in the output of the "explain query plan" that my index is used. Selen From: Simon Slavin To: Selen

Re: [sqlite] Query planning with concatened vs single index

2013-01-14 Thread Selen Schabenberger
By the way I am using the SQLite version 3.7.15.2. I do not see the same behaviour with the version 3.6. Selen From: Selen Schabenberger To: Simon Slavin ; General Discussion of SQLite Database

Re: [sqlite] Concurrent read performance

2013-01-14 Thread Wayne Bradney
Howard,Thanks for the links - mdb/sqlightning looks interesting.Before I dive in, however:1. As I understand it, sqlightning is a drop-in replacement for SQLite.Interop.dll, and I can still use System.Data.SQLite as my high-level interface?2. Is there a compiled release available?3. How often

Re: [sqlite] Concurrent read performance

2013-01-14 Thread Howard Chu
Wayne Bradney wrote: Howard,Thanks for the links - mdb/sqlightning looks interesting.Before I dive in, however:1. As I understand it, sqlightning is a drop-in replacement for SQLite.Interop.dll, and I can still use System.Data.SQLite as my high-level interface? Right. 2. Is there a compiled

[sqlite] Need help with query

2013-01-14 Thread Kai Peters
Hi all, given CREATE TABLE masterlanguages ( ID integer primary key autoincrement, Key1varchar not null, Key2varchar not null, ISOCode varchar not null, Description varchar not null, MaxCharsinteger default 0 ); insert into masterlanguages values

Re: [sqlite] Need help with query

2013-01-14 Thread Yongil Jang
like this? sqlite> SELECT t1.key1, t1.key2, t2.description FROM MASTERLANGUAGES as t1, MASTERLANGUAGES as t2 WHERE t1.ISOCode = 'DEU' and t2.ISOCode = 'ENG' and t1.key1 = t2.key1 and t1.key2 = t2.key2; FORM1|SAVE_BUTTON|Save FORM1|HELP_BUTTON|Help Sorry if my try is wrong. 2013/1/15 Kai Peters

Re: [sqlite] Need help with query

2013-01-14 Thread Kai Peters
On Tue, 15 Jan 2013 14:57:42 +0900, Yongil Jang wrote: > SELECT t1.key1, t1.key2, t2.description FROM MASTERLANGUAGES as t1, > MASTERLANGUAGES as t2 WHERE > t1.ISOCode = 'DEU' and t2.ISOCode = 'ENG' and t1.key1 = t2.key1 and t1.key2 = > t2.key2; that is very close - just needed to add