[sqlite] Index without backing table

Is there a way to have index without table? Is it possible to achieve this with virtual talbe tricks but without implementing B-tree myself? I need this both for space effciency and to minimize disk I/O (by avoiding table updates). ___ sqlite-users

Re: [sqlite] Index without backing table

31 October 2014, 14:19:56, by "Richard Hipp" : > I don't really understand what you are asking, but I suspect that > http://www.sqlite.org/withoutrowid.html is probably the answer you are > looking for. I have just realized, how stupid my question is. Actually I don't even

Re: [sqlite] Index without backing table

e to have more than one index or inefficient table scans, but it will also give some advantage in special cases like mine. - About your case, Edward. I believe you also need mapping where Key is Big (text) but Value is small (offset in a file?)?

Re: [sqlite] Index without backing table

> > > Would be nice to have ability to store both key and payload in the > > index. (Let's call it index-only table) > > This could be a feature that sets some limitations on a table, like > > being unable to have more than one index or inefficient table scans, > > but it will also give some

Re: [sqlite] Index without backing table

> > On 3 Nov 2014, at 9:50am, Paul <de...@ukr.net> wrote: > > > So, to be clear, WITHOUT ROWID table will have it's PRIMARY KEY > > as a replacement for ROWID and table itself is an index? > > It would appear that the answer is "yes". I'm not going

Re: [sqlite] Index without backing table

3 November 2014, 13:48:30, by "Clemens Ladisch" <clem...@ladisch.de>: > Paul wrote: > > Are additional indices, created for WITHOUT ROWID, potentially less > > efficient and more cumbersome? > > For tables with a rowid, the index stores the indexed column

Re: [sqlite] Index without backing table

3 November 2014, 13:56:36, by "Richard Hipp" <d...@sqlite.org>: > On Mon, Nov 3, 2014 at 6:48 AM, Clemens Ladisch wrote: > > > Paul wrote: > > > Are additional indices, created for WITHOUT ROWID, potentially less > > > efficient and more cumberso

Re: [sqlite] Index without backing table

> On Mon, 03 Nov 2014 11:50:17 +0200 > Paul <de...@ukr.net> wrote: > > > > > Would be nice to have ability to store both key and payload in the > > > > index. (Let's call it index-only table) > > > > This could be a feature that sets some limita

Re: [sqlite] insert or ignore with foreign keys

> > I guess the example below shows the intended behaviour for Sqlite? > > PRAGMA FOREIGN_KEYS=1; > CREATE TABLE t1 ( > id INTEGER PRIMARY KEY > ); > > CREATE TABLE t2( > id INTEGER PRIMARY KEY, > t1_id INT NOT NULL, > CONSTRAINT fk FOREIGN KEY(t1_id) REFERENCES t1(id) > ); > > INSERT INTO t1

Re: [sqlite] Corrupting pointers to the lookaside smallacator

We observe very similar problem. #1 0x087ec9f7 in sqlite3VXPrintf () #2 0x087f816d in sqlite3MPrintf () #3 0x088781e5 in sqlite3VtabFinishParse () #4 0x0885190f in yy_reduce () #5 0x0884d4d8 in sqlite3Parser () #6 0x087fc0ce in sqlite3RunParser () #7 0x088aa396 in sqlite3Prepare () #8

Re: [sqlite] Corrupting pointers to the lookaside smallacator

Here is how it looks with debug symbols are on: #0 0x28c4113e in memcpy () from /lib/libc.so.7 #1 0x08854c20 in sqlite3StrAccumAppend (p=0xfffe8548, z=0x2c3fffda "vtb_enyqkyxs USING vtable_module_343", N=41) at sqlite3.c:21563 #2 0x087edf30 in sqlite3VXPrintf (pAccum=0xfffe8548, bFlags=1,

Re: [sqlite] Read overflow in CREATE VIRTUAL TABLE query (formerly Corrupting pointers to the lookaside smallacator)

. But analyzing core file it seems like very much an sqlite bug :/ Tell me if you need more info on this. Thanks. > On 11/27/2014 03:20 PM, Paul wrote: > > Here is how it looks with debug symbols are on: > > > > #0 0x28c4113e in memcpy () from /lib/libc.so.7 > > #1 0x08854c20

Re: [sqlite] Read overflow in CREATE VIRTUAL TABLE query (formerly Corrupting pointers to the lookaside smallacator)

Thank you very much, Dan! > On 11/27/2014 05:56 PM, Paul wrote: > > Currently we use various versions of SQLite: > > > > SQLite version 3.8.0.1 2013-08-29 17:35:01 > > SQLite version 3.8.2 2013-12-06 14:53:30 > > SQLite version 3.8.6 2014-08-15 11:46:33 > >

Re: [sqlite] seeking advice

Hello, Rene > Hi there, > > I have to store and retrieve up to 2000 parameters. > The parameters can have real and integer values. > The max. change rate is 100ms and the max. duration is up to some hours. > > The simple solution would be to use plain binary files. It's fast but not >

[sqlite] How to speed up database open

nitialization time will be delayed until the first query, so I guess there is nothing specific about these pragmas. Thanks, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up database open

Hello, Simon. > > On 11 Dec 2014, at 11:51am, Paul <de...@ukr.net> wrote: > > > I understand, that having them is a must for a decent performance. > > In my specific case I have millions of individual database files. > > This is one, among other reason

Re: [sqlite] How to speed up database open

Hello, Richard, thanks for quick reply. Unfortunately, no, there is no way. On our servers we have big number of entities that represent client data. Data for different clients can be read at any given point of time by clients and by a bunch of daemons responsible for different maintenance

Re: [sqlite] How to speed up database open

sqlite_master; Thanks, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up database open

Hi Ryan, thanks for reply. > > On 2014/12/11 13:51, Paul wrote: > > In my specific case I need to open database as fast as possible. > > Usual working cycle: open -> select small data set -> close. > > It is irrelevant how much time it takes to open databas

Re: [sqlite] How to speed up database open

> On Thu, Dec 11, 2014 at 10:58 AM, Paul <de...@ukr.net> wrote: > > > > > I have yet to try and test if dropping stat tables worth the effort. > > > > Most of the work is involved in loading sqlite_stat4. On the other hand, > most of the benefit comes f

Re: [sqlite] How to speed up database open

> > On 2014/12/11 17:58, Paul wrote: > > > >> On 2014/12/11 13:51, Paul wrote: > >> I have yet to try and test if dropping stat tables worth the effort. Some > >> databases in fact can grow pretty big, up to few > >> hundred of megabytes//...

Re: [sqlite] How to speed up database open

> > On Thu, Dec 11, 2014 at 10:58 AM, Paul <de...@ukr.net> wrote: > > > > > > > > I have yet to try and test if dropping stat tables worth the effort. > > > > > > > Most of the work is involved in loading sqlite_stat4. On the other

Re: [sqlite] Feature Request - RowCount

tually be implemented as an SQLite add-on, via virtual table. So for example, instead of doing SELECT COUNT(*) FROM XXX; you could do SELECT count FROM vtb_row_count_cache WHERE table = 'XXX'; Just and idea... Regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] '.timer on' in the shell tool

rned a lot of data. Specifically that data was writen as an output to your console. If this is the case, a lot of system time was wasted just to write the results. It may be because each written line calls fflush() on stdout. Regards, Paul ___ sqlite-

[sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hello, dear developers Recently I've stumbled upon a very rare and strange bug. The result of this is abnormal memory usage, that does not allow us to remove fair number of rows from a table due to the limit of memory, available for 32bit process. This is strange, because database size is

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Sorry, I've forgot to mention my sqlite version, here it is: 3.8.7.1 2014-10-29 13:59:56 3b7b72c4685aa5cf5e675c2c47ebec10d9704221 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hello Richard. > On Mon, Dec 15, 2014 at 11:11 AM, Paul <de...@ukr.net> wrote: > > > > Hello, dear developers > > > > Recently I've stumbled upon a very rare and strange bug. > > The result of this is abnormal memory usage, that does not allow us to > >

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hi Dan. > On 12/15/2014 11:59 PM, Dan Kennedy wrote: > > On 12/15/2014 11:11 PM, Paul wrote: > >> Hello, dear developers > >> > >> Recently I've stumbled upon a very rare and strange bug. > >> The result of this is abnormal memory usage, that does

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

> > > > > > The memory is being used by the statement journal, which you have in > > > memory. If the app did not set "journal_mode=memory" and > > > "temp_store=memory", SQLite would create a really large temp file > > > instead of using memory. Which would still be sub-optimal, but might >

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

saying there is no YYY savepoint? Or the journal can be truncated > when omiting BEGIN oe XXX but not when they are both present? Please don't mind my last message. I understand now, what is going on. Yet this limitation is pretty depressing. Is there no way in the future for things to change? Can't journal be truncated, or modified pages be merged, after each consequential implicit sub-transaction (YYY) release, while they are still in the cache? Is there any hope for me, except deferred FKs or DELETE FROM foo WHERE id IN (...)? Best regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

I want to add even more input for this issue. I understand why there is implicit savepoint, when I remove row from 'parent' table. But why is this also true for a 'child' table when I perform 'INSERT OR REPLACE'? Removing FK reference disables journal growth. I don't understand... I have a new

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

> On 12/16/2014 03:08 PM, Paul wrote: > >>>>> The memory is being used by the statement journal, which you have in > >>>>> memory. If the app did not set "journal_mode=memory" and > >>>>> "temp_store=memory", SQLite

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

I want to confirm that issue is fixed for me. Thanks again, Dan! Please ignore this update, patch fixes this problem as well. > I want to add even more input for this issue. > I understand why there is implicit savepoint, when I remove row from 'parent' > table. > But why is this also true

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

> > I want to confirm that issue is fixed for me. > Thanks again, Dan! > > > Please ignore this update, patch fixes this problem as well. > > > I want to add even more input for this issue. > > I understand why there is implicit savepoint, when I remove row from > > 'parent' table. > > But

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hi, Dan. > On 12/18/2014 02:41 PM, Paul wrote: > >> I want to confirm that issue is fixed for me. > >> Thanks again, Dan! > >> > >> > >> Please ignore this update, patch fixes this problem as well. > >> > >>> I want to add eve

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

nt rollback may be required is because > any replaced rows will be removed before SQLite has a chance to figure > out if the INSERT actually does violate the PK constraint. > I see, thank you for explanation, Dan. Best regards, Paul ___ sq

Re: [sqlite] binding multiple values in a query

parser will have some usefull information aboud what is expected in following bind. Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite transaction time limit

You are probably asking for 8 July 2015, 15:24:18, by "Iryna Atamanova" : > Hi guys, > > I will be very appreciate for your help. > > We have found the next feature of sqllite - it transaction takes more that > 5 seconds, the database has

[sqlite] Regarding ALTER TABLE doc page

In the footer of it is said: It is important that both of the above procedures be run from within a transaction to prevent other processes from accessing the database file while the schema change is only partially complete. But this statement is

[sqlite] Is 'synchronous' pragma now being set on per database basis?

PRAGMA synchronous = OFF;' is set. Is there a specific reason for keeping fsync() for directory? Seqlite version that we use is: sqlite3-3.8.7_1 Thanks, Paul

[sqlite] Is 'synchronous' pragma now being set on per database basis?

> On 3/16/15, Paul wrote: > > > Is doc page is > > outdated and 'synchronous' pragma is now set for each databse separately? > > It has always been that way. The documentation has recently been > updated to clarify that point. See > http://ww

[sqlite] Is 'synchronous' pragma now being set on per database basis?

> On 3/16/15, Paul wrote: > > when the usage is > > > > (1) open database > > (2) execute 'PRAGMA synchronous = OFF;' > > (3) attach database X > > (4) execute 'PRAGMA X.synchronous = OFF;' > > (5) begin transaction > > (6) do some insert

[sqlite] Why is empty string not equal to empty blob?

length(my_blob) FROM FOO; returns 0 for both empty string('') and empty blob (x'') alike? And what is the reason behind this difference? Thanks, Paul

[sqlite] Why is empty string not equal to empty blob?

> On 3/19/15, Paul wrote: > > Maybe this question was already asked and explained. > > Or maybe it is documented somewhere (could not fiund it). > > Sorry, if this is the case, but why does > > > > SELECT '' = x''; > > > > yields 0?

[sqlite] Why is empty string not equal to empty blob?

> On 19 Mar 2015, at 3:19pm, Paul wrote: > > > This may cause very nasty hard-to-find bugs, since SQLite allows to store > > any content inside BLOB field: > > > > sqlite> create table foo(a int, b int, primary key(a, b)); > > sqlite>

Re: [sqlite] Conditional JOIN

dx DESC; Anyway, thank you for help! Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Primary Key without DataAnnotation for Windows Runtime

If I understood you right... 1) Create teable without primary key. 2) Separately create unique index on the column you want to be primary key. > Hi, > > > > we are building an App for Windows 8 and want to use sqlite as local DB. We > want to separate our Models from our DB Engine. So we need

Re: [sqlite] how to write this commands?

> update adla1 set PFLOPF=(SELECT pflopf from adl where adla1.ref=adl.ref) > where select count(adl.ref) from adl=1; A bit optimized version... UPDATE adla1 SET pflopf = (SELECT pflopf FROM adl WHERE adl.ref = adla1.ref) WHERE (SELECT COUNT(*) FROM (SELECT 1 FROM adl WHERE adl.ref =

Re: [sqlite] how to write this commands?

> UPDATE adla1 > SET pflopf = ( > SELECT pflopf > FROM adl > WHERE adl.ref = adla1.ref) > WHERE ( > SELECT COUNT(*) > FROM ( > SELECT 1 FROM adl > WHERE adl.ref = adla1.ref > LIMIT 2 > ) > ) = 1; > > Not all sure what LIMIT 2 does there. I think a SQL-92 version > would be > Limit,

Re: [sqlite] Building multiple indexes in one scan over a table

will your database become and how will it affect the performance. I'm not even sure if 'index creation' be any faster... Make your own research if you like. Just wanted to share what came to my crazy mind :) Good luck! Paul ___ sqlite-users mailing list sqlite-u

Re: [sqlite] Hexadecimal integer literals

break any existing logic. Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how best to determine # of rows in a table

What about canonical SELECT COUNT(*) FROM table; ? 27 August 2014, 16:51:23, Author "Mark Halegua" > this may seem like a small issue, but I'm not sure if the solutions I've > found > on the web will do what I want in a low memory situation.I'd like to iterate

Re: [sqlite] fts5

> Fts5 is still in the experimental stage at the moment. > > If anybody has any ideas for useful features, or knows of problems with > FTS4 that could be fixed in FTS5, don't keep them to yourself! > It would be awesome to be able to select document ids in the content-less FTS4 table.

[sqlite] PRAGMA cache_size;

, will it mean every database (attached or main) have its own separate cache of size 100 * page_size or is it single, shared cache? Thank you, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

Re: [sqlite] PRAGMA cache_size;

> On Fri, Sep 12, 2014 at 5:00 AM, Paul <de...@ukr.net> wrote: > > > > > I have a few questions regarding 'cache_size' PRAGMA: > > > > (Q1) Does this PRAGMA affects all attached databases or should I set it > > for each > > attached databa

[sqlite] Atomic database structure initialization

if this, I think, common problem has standard ways of solving. Regards, Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > > My goal is to make structure initialization of an *abstract* database > > atomic. > > [...] if database file is missing it must be created and initialized. > > <http://www.sqlite.org/transactional.html> > > Just do the check for the da

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> Paul wrote: > >>> My goal is to make structure initialization of an *abstract* database > >>> atomic. > >>> [...] if database file is missing it must be created and initialized. > >> > >> <http://www.sqlite

Re: [sqlite] Atomic database structure initialization

> > On 19 Sep 2014, at 8:34am, Paul <de...@ukr.net> wrote: > > > if database file is missing it must be created and initialized. > > For that purpose I need to provide a guarantee that *on_create* callback > > will be called strictly once. > > Can you

Re: [sqlite] Atomic database structure initialization

> > There is also a PRAGMA user_version (see > http://www.sqlite.org/pragma.html#pragma_schema_version) which will let you > store a number in the database header so you can keep track of what version > of the "user schema" you have implemented in the database. Initially, when > the database

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> pragma user_version; > >> > >> returns a single row with a > single value which is the version, and the command, > >> > >> pragma > user_version=n; > >> > >> lets you change it to n. Perhaps

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> pragma user_version; > >> > >> returns a single row with a single value which is the version, and the > >> command, > >> > >> pragma user_version=n; > >> > >> lets you change it to n. Perhaps

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > > I can check whether user_version matches magic number without transaction. > > No. Executing "PRAGMA user_version" will start an automatic transaction > if you didn't already start an explicit one. > > > Only when user_version does not m

Re: [sqlite] Atomic database structure initialization

> > On 22 Sep 2014, at 1:13pm, Paul <de...@ukr.net> wrote: > > > The only thing I am worried about is whether > > > > pragma user_version=n; > > > > respects transactions and will be rolled back automatically in case > > if something happ

Re: [sqlite] Atomic database structure initialization

> > > Paul wrote: > > > I can check whether user_version matches magic number without transaction. > > > > No. Executing "PRAGMA user_version" will start an automatic transaction > > if you didn't already start an explicit one. > > > &

Re: [sqlite] Atomic database structure initialization

Thank you for help, guys! I knew that sqlite is a great piece of software, now I have even more proofs :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about LIMIT 1 fitness on lookups by unique index

now, finally I want to make thing clear. Thanks, Paul

[sqlite] Question about LIMIT 1 fitness on lookups by unique index

>From now on I wont use "LIMIT 1" in these cases any more. Such queries will be more readable and surely not slower. Thank you for explanation! Paul 5 February 2016, 14:21:35, by "Richard Hipp" : > On 2/5/16, Paul wrote: > > > > Does it have any

[sqlite] What's the reason for alias names not being visible in subqueries?

Hello! I am curious what is a particular reason that aliased columns in a query not visible to sub-queries? CREATE TABLE foo( id INTEGER, bar INTEGER ); INSERT INTO foo VALUES(1, 2), (3, 4); SELECT 1 as super_id, (SELECT bar FROM foo WHERE id = super_id); Gives an error: Error: no

[sqlite] What's the reason for alias names not being visible in subqueries?

emens Ladisch" : > Paul wrote: > > I am curious what is a particular reason that aliased columns in a query > > not visible to sub-queries? > > Because the SQL standard says so. > > > Of course it is possible to duplicate expression in sub-query ... > > B

[sqlite] What's the reason for alias names not being visible in subqueries?

, Feb 8, 2016 at 3:38 AM, Paul wrote: > > I see, thank you for pointing out. > > > > I wanted to use it on table with conditional relations with 3 different > > child tables. > > Though I could use a trick and fit data selection into one query, > > effici

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

Let's say I have a following database structure: CREATE TABLE properties ( ?name? TEXT NOT NULL, ?value TEXT, ?PRIMARY KEY(name) ) WITHOUT ROWID; CREATE TABLE foo ( ?id??? TEXT NOT NULL, ?PRIMARY KEY(id) ); CREATE TRIGGER foo_inserted ? AFTER

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

Oh, thanks for pointing me to this statement! Didn't know that 'OR IGNORE' is an alias of 'ON CONFLICT IGNORE'. 17 February 2016, 17:32:32, by "Dan Kennedy" : > On 02/17/2016 08:47 PM, Paul wrote: > > Let's say I have a following database structure: > > &g

[sqlite] Why is a separate journal file needed ?

Good point! I can assume that the problem would be a sparsity of database file. If you mix normal pages and journal then database will be fragmented. You can't avoid it. Even if you start writing journal at the end of file, hoping that you can later truncate it at commit, there is no way to

[sqlite] Are there practical limits to a not-so simple schema?

If you ever going to use ANALYZE on your database and database is going to be open frequently (like once per request) consider dropping sqlite_stat3 and sqlite_stat4 tables. SQLite reads content of those tables on each open. Number of tables greatly contributes to amount of data stored in

[sqlite] UPSERT

What is the reason to pay extra overhead if it can be omitted? And actually, SQLite does a great job with concurrency, from my experience. There's really smart locking model employed. And this is really tedious to work around: 1) You cannot disable FK constraints inside a transaction 2)

Re: [sqlite] sqlite on flash devices

-- -Paul ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on flash devices

levelling which of course will prolong life. - -Paul On Thu, 2008-10-02 at 17:44 -0400, Matthew L. Creech wrote: > On Thu, Oct 2, 2008 at 4:20 AM, Paul McMahon <[EMAIL PROTECTED]> wrote: > > What's the consensus on using sqlite with flash based storage such as sd > > card

Re: [sqlite] sqlite on flash devices

I thikn you mean supplying a sqlite3_vfs to the sqlite3_open_v2 call; will look into it. Anyone know where there are examples of this? - -Paul On Fri, 2008-10-03 at 01:03 +0400, Alexey Pechnikov wrote: > Hello! > > I'm using SQLIte on a few dozens winmobile devices a few years.

Re: [sqlite] sqlite on flash devices

is to compile SQLITE with -DSQLITE_TEMP_STORE=3 to keep sqlite temporary files in memory. - -Paul On Thu, 2008-10-02 at 13:36 -0600, J Glassy wrote: > Paul, > here are a few odd thoughts on this: > --Flash memory devices in general are subject to 'finite' numbers of > I/O events, most

Re: [sqlite] update or replace ...

Whatever the case, it will be, on average, 1.5x time less efficient than a 'I-Hope-Would-Be' UPSERT. It all depends on the probability of INSERT to fail.  In the scenario, where updates are frequent and inserts are rare, INSERT + UPDATE approach takes ~2x performance overhead. In most cases we

[sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

I've traced this issue down to the simplest test case: CREATE TABLE IF NOT EXISTS foo (  id  INTEGER,  baz INTEGER,  PRIMARY KEY(id) ); CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id); CREATE TABLE IF NOT EXISTS bar (  foo INTEGER,  PRIMARY KEY(foo),  FOREIGN KEY(foo) REFERENCES foo(id)

[sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

I've traced this issue down to the simplest test case: CREATE TABLE IF NOT EXISTS foo ( id INTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE INDEX IF NOT EXISTS baz_foo_idx ON foo(baz, id); CREATE TABLE IF NOT EXISTS bar ( foo INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

Yes, fixed in pre-release snapshot 201610041220. Thank you. > On 10/5/16, Richard Hipp wrote: > > On 10/5/16, Clemens Ladisch wrote: > >> stop > >> > >> This looks like a bug. > >> > > > > I think it might be fixed on trunk. I was just trying to bisect... > > I think this

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

To add to that, EXPLAIN QUERY PLAN shows that covering index will be used: sqlite> EXPLAIN QUERY PLAN SELECT id FROM foo WHERE baz = 1 AND id IN (SELECT foo FROM bar) LIMIT 1; selectidorder fromdetail

Re: [sqlite] Performance degradation in query planner in SQLite 3.14.2 (vs SQLite 3.10.2)

> Paul wrote: > > I've traced this issue down to the simplest test case: > > > > CREATE TABLE IF NOT EXISTS foo > > ( > > id INTEGER, > > baz INTEGER, > > PRIMARY KEY(id) > > ); > > > > CREATE INDEX IF NOT EXISTS baz_

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Marc L. Allen wrote: > > Maybe I'm missing something, but... > > > > ORDER BY id > > > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > > to find enough records where the LEFT

[sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

These are the queries: CREATE TABLE foo( idINTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE TABLE bar( foo INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE ); EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Paul > Gesendet: Donnerstag, 17. November 2016 13:58 > An: General Discussion of SQLite Database > Betreff: [sqlite] Query Planner fails to recognise efficient strategy when > '=' condition give

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Richard Hipp wrote: > > On 11/17/16, Paul wrote: > >> That's why there was a LEFT JOIN in the first place, but as it seems, it > >> wasn't that good idea. > > > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > &g

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Paul wrote: > > > >> On 11/17/16, Richard Hipp wrote: > >> > On 11/17/16, Paul wrote: > >> >> That's why there was a LEFT JOIN in the first place, but as it seems, > >> >> it > >> >> wasn't that good

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> Why don't you just explicitly sort by bar.foo? > > > sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN > foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10; > 0|0|0|SCAN TABLE bar > 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) > sqlite> I have

Re: [sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes

Thanks! That explains a lot. For some reason I thought that 'SELECT COUNT() FROM ' is optimised. > Gonna take a stab and answering this. > http://www.sqlite.org/opcode.html > > The explain output for select count() from foo; uses the "Count" opcode. The > description for that is > "Store

[sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes

I am not very familiar with the SQLite internals, but I believe that index structure is similar to that of a table, ie it's a B-TREE with a root containing a node count value. If so, then queries like SELECT COUNT() FROM FOO WHERE <...>; can be optimised the same way that queries like SELECT

[sqlite] Use of sqlite3_bind_int() vs sqlite3_bind_int64()

According to datatypes page https://sqlite.org/datatype3.html SQLite choses width of the integer automatically. Does it mean that if I let's say want to bind a number 5 in the query that inserts/updates rows it will be stored in database as 1-byte integer regardless of the use of

Re: [sqlite] Use of sqlite3_bind_int() vs sqlite3_bind_int64()

Thank you very much for replying so quickly! > On 3/28/17, Paul wrote: > > According to datatypes page https://sqlite.org/datatype3.html SQLite choses > > width of the integer automatically. Does it mean that if I let's say want to > > bind a number 5 in the query that

Re: [sqlite] Problem with the new pointer-passing interface

To me it seems like that string is very tightly coupled with the actual pointer being bound. I think it's a good idea, in case you cannot make it a literal or static, to keep it with an object whose pointer you bind. Hi, I came across a problem with the new pointer-passing interface,

Re: [sqlite] SQLite in memory

If by any chance you have access to Linux or alike, you can just mount a ramfs and move database file over there. It is a usual file system that lives in RAM. This will 100% guarantee you that no disk access will be made by SQLite. 18 May 2017, 08:18:47, by "Gabriele Lanaro"

Re: [sqlite] PRAGMA optimize; == no busy handler?

on *every* query, just to be sure. -Paul > >Don't do that > Mostly sure, but there's some cases SQLite will skip the busyhandler and > immediately return, but they're not permanently-busy conditions. We have > multiple threads across multiple processes using the database > &g

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 In all of the few dozens of schemas. Rowid logic is almost perfect. In extremely rare cases, when primary key id is exposed outside of database, a custom table that keeps last allocated id is used. 16 March 2018, 17:37:31, by "Richard Hipp" : This is a survey, the

  1   2   3   4   5   6   7   8   >