Re: [sqlite] Sqlite chooses the wrong index for query after upgrading to 3.8.3.1

On Fri, Mar 28, 2014 at 7:38 PM, Péli Gergely wrote: > On Fri, 28 Mar 2014 19:29:52 -0400 > Richard Hipp wrote: > > What happens if you omit the INDEXED BY clause and instead run ANALYZE? > > Nice, it starts to use the right index. But ANALYZE shouldn't be

Re: [sqlite] Sqlite chooses the wrong index for query after upgrading to 3.8.3.1

On Fri, 28 Mar 2014 19:29:52 -0400 Richard Hipp wrote: > What happens if you omit the INDEXED BY clause and instead run ANALYZE? Nice, it starts to use the right index. But ANALYZE shouldn't be necessary in such a simple case, right? Gergely Peli

Re: [sqlite] Sqlite chooses the wrong index for query after upgrading to 3.8.3.1

On Fri, Mar 28, 2014 at 5:39 PM, Gergely Peli wrote: > sqlite> CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER, d BLOB); > sqlite> CREATE INDEX appropriate ON t (a, b, c); > sqlite> CREATE INDEX inappropriate ON t (b, c); > sqlite> EXPLAIN QUERY PLAN SELECT d FROM t WHERE a = ?

[sqlite] Sqlite chooses the wrong index for query after upgrading to 3.8.3.1

Hi, I experienced a serious performance regression with a rather simple query. The situation can be reproduced as: SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER, d

Re: [sqlite] SQLite in Windows Phone Runtime Component

Arne Fischer wrote: > > { "AreFileApisANSI", (SYSCALL)AreFileApisANSI, 0 }, > > And the Error is: > Error 2 error C2065: 'AreFileApisANSI' : undeclared identifier > In order to compile SQLite for Windows Phone 8, the following extra defines are normally necessary:

Re: [sqlite] Use of AUTOINCREMENT

On 28 Mar 2014, at 2:09pm, Richard Hipp wrote: > The current "burn rate" on the SQLite repository is about 3650 record IDs > per year. Let's assume 10x the burn rate. Even then, it is another 58,000 > years or so before the 32-bit signed integer overflows. At that burn rate

Re: [sqlite] Use of AUTOINCREMENT

On Fri, 28 Mar 2014 12:56:59 +0100 "Kleiner Werner" wrote: > > > Hello > I saw some discussions here about using the word "AUTOINCREMENT" for > a primary key or not. > Sometimes the meaning is "INTEGER PRIMARY KEY" is enough, because > this will also make an auto increment

Re: [sqlite] Use of AUTOINCREMENT

On Fri, Mar 28, 2014 at 1:50 PM, Simon Slavin wrote: > But this is not a documented specification for SQLite. It is allowed to > use any integer which is currently unused. I have no idea whether it's > allowed to use negative integers. > This is not a definitive answer

Re: [sqlite] Use of AUTOINCREMENT

On Fri, Mar 28, 2014 at 2:24 PM, Richard Hipp wrote: > SQLite prefers small positive rowids, since such rowids take up less space > on disk using the variable-length integer encoding. ( > http://www.sqlite.org/fileformat2.html#varint). But there are no > guarantees. > i guess

Re: [sqlite] Use of AUTOINCREMENT

On Fri, Mar 28, 2014 at 9:56 AM, Stephan Beal wrote: > On Fri, Mar 28, 2014 at 2:24 PM, Richard Hipp wrote: > > > SQLite prefers small positive rowids, since such rowids take up less > space > > on disk using the variable-length integer encoding. ( > >

Re: [sqlite] Use of AUTOINCREMENT

On 3/28/2014 7:56 AM, Kleiner Werner wrote: Does this mean, if there are 10 rows with id 1 -10 and I delete row with id 5, then the next insert will be ID = 5? Or is next ID = 11? Here's a more interesting example: you have ten rows with ids 1-10. You delete the row with id of 10 (the last

Re: [sqlite] Use of AUTOINCREMENT

On Fri, Mar 28, 2014 at 8:52 AM, Stephan Beal wrote: > On Fri, Mar 28, 2014 at 1:50 PM, Simon Slavin > wrote: > > > But this is not a documented specification for SQLite. It is allowed to > > use any integer which is currently unused. I have no

Re: [sqlite] Use of AUTOINCREMENT

Stephan Beal wrote: > On Fri, Mar 28, 2014 at 1:50 PM, Simon Slavin wrote: >> But this is not a documented specification for SQLite. It is allowed to >> use any integer which is currently unused. I have no idea whether it's >> allowed to use negative integers. > > This is

Re: [sqlite] Use of AUTOINCREMENT

On Fri, Mar 28, 2014 at 2:17 PM, Clemens Ladisch wrote: > sqlite> select * from u; > -100 > 1 > Very interesting, thank you :). That's quite subtle. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since

Re: [sqlite] Use of AUTOINCREMENT

On 28 Mar 2014, at 12:23pm, Kees Nuyt wrote: > The next ID will usually be 11, but it is not guaranteed. > One day, it could suddenly be 5. Just to add more to that, the rule is currently along the lines of "New IDs will continue to increment until SQLite runs out of

Re: [sqlite] Problem with Multiple INSERT, SQLite C driver issue ?

On Fri, Mar 28, 2014 at 8:39 AM, Bertini, Denis Dr. wrote: > Hi all, > When using multiple rows insertion from the sqlite interpreter > > for example something like : > > sqlite> > > > INSERT INTO 'tablename' ('column1', 'column2') VALUES > ('data1', 'data2'), >

[sqlite] Problem with Multiple INSERT, SQLite C driver issue ?

Hi all, When using multiple rows insertion from the sqlite interpreter for example something like : sqlite> INSERT INTO 'tablename' ('column1', 'column2') VALUES ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'), ('data1', 'data2'); It works as it should for SQLite

Re: [sqlite] Use of AUTOINCREMENT

On Fri, 28 Mar 2014 12:56:59 +0100, "Kleiner Werner" wrote: > > >Hello >I saw some discussions here about using the word "AUTOINCREMENT" for a primary >key or not. >  >Sometimes the meaning is "INTEGER PRIMARY KEY" is enough, because this will >also make an auto increment

[sqlite] SQLite in Windows Phone Runtime Component

I want to use native c++ code within my WindowsPhone 8 Application. For this purpose I did build a "Windows Phone Runtime Component" to wrap my c++ code. I simply compile my native code with visual studio when I compile the Runtime Component. This took some tweaking but Visual Studio is now

Re: [sqlite] Is this normal behavior for a unique index?

On 28 Mar 2014, at 6:06am, SongbookDB wrote: > Never mind Simon. It turns out that SQLite allows NULLable fields to > participate in UNIQUE indexes. > > I'd screwed up the code I'd made to replace anything undefined with "". > I've changed it, and it's properly

[sqlite] Use of AUTOINCREMENT

Hello I saw some discussions here about using the word "AUTOINCREMENT" for a primary key or not.   Sometimes the meaning is "INTEGER PRIMARY KEY" is enough, because this will also make an auto increment id.   Can someone give me a clarification if "AUTOINCREMENT" is needed or not? In the FAQ I

Re: [sqlite] Is this normal behavior for a unique index?

Never mind Simon. It turns out that SQLite allows NULLable fields to participate in UNIQUE indexes. I'd screwed up the code I'd made to replace anything undefined with "". I've changed it, and it's properly saving empty strings now, and the index is now successfully ignoring identical row insert