[sqlite] Multi-table index ersatz?

On 03/03/2015 06:10 PM, Eric Grange wrote: > Hi, > > I have problem where I need a "multi-table index" ersatz, or maybe a better > data structure :-) > > The problem is as follow: > > - Table A : some fields plus fields A1 & A2 > - Table B : some fields plus fields B1 & B2 > > Both tables

[sqlite] static malloc_zone_t* _sqliteZone_

Hi SQLite users; I have a question about _sqliteZone_ in mem1.c. I notice that the address of this static variable is used in a call to OSAtomicCompareAndSwapPtrBarrier on MacOS and iOS. That system call is declared in OSAtomic.h, which includes a note about the pointer alignment of its

[sqlite] full table scan ignores PK sort order?

On 2015-03-03 02:43 PM, Richard Hipp wrote: > On 3/3/15, Jan Asselman wrote: >> Most of my queries are in the form >> "SELECT * FROM test WHERE a == ? AND b < ?;" >> and use the primary key index so that the rows are returned in the expected >> order without using the ORDER BY statement. > Do

[sqlite] full table scan ignores PK sort order?

Maybe an implicit ORDER BY random() ;) -Urspr?ngliche Nachricht- Von: Mohit Sindhwani [mailto:ml3p at onghu.com] Gesendet: Dienstag, 03. M?rz 2015 18:22 An: General Discussion of SQLite Database Betreff: Re: [sqlite] full table scan ignores PK sort order? On 3/3/2015 6:59 PM,

[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] No diagnostic given for missing column (since 3.7.10 up to 3.8.8.3)

On 03/03/2015 03:57 PM, Andy Gibbs wrote: > Hi, > > The following is a reduced test-case of a problem I have observed: > > CREATE TABLE a(tid,pid); > CREATE TABLE b(tid,pname); > CREATE TEMP TABLE pidmap(newpid INTEGER,oldpid INTEGER); > CREATE TEMP TABLE pnamemap(pid INTEGER,pname TEXT COLLATE

[sqlite] full table scan ignores PK sort order?

YES, you can. Create analyze data: - load your database - analyze - export the sqlite_statn tables Build a new database: - create new database - analyze - import the saved sqlite_statn tables 6.2 Manual Control Of Query Plans Using SQLITE_STAT Tables SQLite provides the ability for advanced

[sqlite] full table scan ignores PK sort order?

Thanks for answering both of my questions. I guess this is similar to the 'Skip-Scan Optimization' mentioned in the documentation. That is what I assumed and explains the difference in query performance. Scanning the table once is faster than scanning the table for each and every value of

[sqlite] No diagnostic given for missing column (since 3.7.10 up to 3.8.8.3)

On Tuesday, March 03, 2015 10:50 AM, Dan Kennedy wrote: > On 03/03/2015 03:57 PM, Andy Gibbs wrote: >> Hi, >> >> The following is a reduced test-case of a problem I have observed: >> >> CREATE TABLE a(tid,pid); >> CREATE TABLE b(tid,pname); >> CREATE TEMP TABLE pidmap(newpid INTEGER,oldpid

[sqlite] full table scan ignores PK sort order?

Thanks for the detailed explanation. I understand that I should not make assumptions about SQL engine internals for typical database usage. I now also understand that by not using the ORDER BY clause, SQLite might one day decide to execute query plans that disrupt the order that I induce from

[sqlite] No diagnostic given for missing column (since 3.7.10 up to 3.8.8.3)

Your expectation is wrong. What follows SELECT is the "result column list", which may contain arbitrary expressions. SQL will attempt to identify all the objects mentioned in the result column list by searching the defined objects of the statement (which includes the definitions of all the data

[sqlite] doc puzzle, on-conflict clause in insert

I was looking at http://www.sqlite.org/lang_insert.html for details on the 'INSERT' statement. In the text, it says: "The optional conflict-clause allows the specification of an alternative constraint conflict resolution algorithm to use during this one INSERT command. See the section titled ON

[sqlite] PRAGMA Synchronous safety

On 2 Mar 2015, at 10:32pm, Doug Nebeker wrote: >> Are you using any PRAGMAs apart from "PRAGMA synchronous" ? > > PRAGMA temp_store=1 (file) > PRAGMA cache_size=2000 > PRAGMA page_size=4096 > sqlite3_soft_heap_limit( 1GB ) > > Those are the only non-default settings. I see nothing in there

[sqlite] Multi-table index ersatz?

Let's construct this example from the reverse. The object is to avoid the sort at the end (sorting "millions" to return 100 is a bad tradeoff), so the B table needs to be visited in B1 order. -> outer loop = B -> inner loop = A -> index B on (B1,...) The join is on A2 = B2 ->index A on

[sqlite] Multi-table index ersatz?

Eric Grange wrote: > select ...some fields of A & B... > from A join B on A.A2 = B.B2 > where A.A1 = ?1 > order by B.B1 > limit 100 > > Without the limit, there can be tens of thousandths resulting rows, Even with the limit, all the tens of thousands rows must be sorted. > without the A1

[sqlite] Multi-table index ersatz?

Yes A2 & B2 are already indexed (individually and in composite indexes) The problem is that this indexing is not selective enough when taken in isolation. Le 3 mars 2015 12:36, "Simon Davies" a ?crit : > On 3 March 2015 at 11:10, Eric Grange wrote: > > > > Hi, > > > > I have problem where I

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

> I can't confirm that 100% off the top of my head but I'm uncoordinated > enough to repeatedly confuse the bind and column value API calls and use > 0-based indices for both and haven't noticed any really untoward behaviour > (beyond my code not working and requiring fixing). I know the feeling,

[sqlite] Multi-table index ersatz?

On 3 Mar 2015, at 11:10am, Eric Grange wrote: > With indexes on A & B, the performance of the above is not very good, as > indexing A1 is not enough, and indexing B1 is not enough either, so no > query plan is satisfying. The B1 index isn't going to be used. Here is your query: select

[sqlite] Multi-table index ersatz?

Hi, I have problem where I need a "multi-table index" ersatz, or maybe a better data structure :-) The problem is as follow: - Table A : some fields plus fields A1 & A2 - Table B : some fields plus fields B1 & B2 Both tables have several dozen millions of rows, and both are accessed

[sqlite] With recursive question

At 04:05 01/03/2015, you wrote: >On 2/28/2015 7:22 PM, Jean-Christophe Deschamps wrote: >>Once again thank you very much Igor. I was making my life miserable >>trying to scan the "tree" from the other end. > >That, too, could be arranged. Something along these lines: > >with recursive FileDirs as

[sqlite] full table scan ignores PK sort order?

The subquery is the index access (partial table scan), which is performed once for each and every value in your IN list for the column a (in effect, the IN list is transformed into an ephemeral table and joined to your test table). Since you did not declare an index for your primary key

[sqlite] full table scan ignores PK sort order?

At 11:27 03/03/2015, you wrote: >- the full table scan returns rows in rowID order, which is the order >in which the rows were added to the table `--- No and no. An SQL engine doesn't guarantee any row "order" unless you explicitely force an ORDER BY clause. Think of row order as random,

[sqlite] full table scan ignores PK sort order?

On Tue, Mar 3, 2015 at 11:27 AM, Jan Asselman wrote: > - the full table scan returns rows in rowID order, which is the order in > which the rows were added to the table > You cannot rely on that (the "the order in which rows were added" part). At best it's an implementation detail. If you want

[sqlite] Multi-table index ersatz?

On 3 March 2015 at 11:10, Eric Grange wrote: > > Hi, > > I have problem where I need a "multi-table index" ersatz, or maybe a better > data structure :-) > > The problem is as follow: > >- Table A : some fields plus fields A1 & A2 >- Table B : some fields plus fields B1 & B2 > > Both

[sqlite] full table scan ignores PK sort order?

On 3 Mar 2015, at 10:59am, Jean-Christophe Deschamps wrote: > An SQL engine doesn't guarantee any row "order" unless you explicitely force > an ORDER BY clause. Think of row order as random, where rowid order is just a > possibility among zillions others. Of course neither SQLite nor other

[sqlite] full table scan ignores PK sort order?

Thank you! I think I get it: - primary key is nothing but a UNIQUE constraint (in my case comparable to a separate index == table with columns a, b and rowID) - the full table scan returns rows in rowID order, which is the order in which the rows were added to the table There is just one more

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

On Tue, Mar 3, 2015 at 3:33 AM, Paolo Bolzoni wrote: >> I can't confirm that 100% off the top of my head but I'm uncoordinated >> enough to repeatedly confuse the bind and column value API calls and use >> 0-based indices for both and haven't noticed any really untoward behaviour >> (beyond my

[sqlite] List duplication

On 2015-03-03 06:14 AM, Mike Owens wrote: > Okay, I blocked the sqlite-users at sqlite.org address in the to address > so if it is sent alone, it will be blocked. This is working perfectly, thanks!

[sqlite] No diagnostic given for missing column (since 3.7.10 up to 3.8.8.3)

Hi, The following is a reduced test-case of a problem I have observed: CREATE TABLE a(tid,pid); CREATE TABLE b(tid,pname); CREATE TEMP TABLE pidmap(newpid INTEGER,oldpid INTEGER); CREATE TEMP TABLE pnamemap(pid INTEGER,pname TEXT COLLATE BINARY); (Please note that the tables are usually

[sqlite] full table scan ignores PK sort order?

On 3/3/15, Jan Asselman wrote: > > Can I copy the "statistics tables" from one database file to another? > Yes. You have to run "ANALYZE sqlite_master;" first to actually create the tables, but then you can populate the tables with data copied from a different database. -- D. Richard Hipp drh

[sqlite] PRAGMA Synchronous safety

On 3/2/15, Doug Nebeker wrote: > >> Depends on whether you are using WAL mode or not. > > I am not using WAL. > Then there is little performance impact from using PRAGMA synchronous=FULL versus PRAGMA synchronous=NORMAL. Both should work. I do not understand why you are seeing errors. Have you

[sqlite] Sqlite and threads/multiple applications

ok, thank you all! > Will Fong > 2 mars 2015 01:23 > Hi Olivier, > > As other people have already mentioned, the operating system doesn't > matter. Pick the one you're most comfortable or interest in using. The > most reliable or secure OS is worthless if you don't

[sqlite] System.Data.SQLite.DLL version 1.0.95.0 Bug

Michal wrote: > > Thank you for looking at the issue. I am not sure what to do with > the link supplied below, I think that is way above my skill level. > The fix will be included in the next release of System.Data.SQLite. -- Joe Mistachkin

[sqlite] full table scan ignores PK sort order?

On 3/3/15, Jan Asselman wrote: > > Most of my queries are in the form > "SELECT * FROM test WHERE a == ? AND b < ?;" > and use the primary key index so that the rows are returned in the expected > order without using the ORDER BY statement. Do not rely on this behavior! It might change at any

[sqlite] System.Data.SQLite.DLL version 1.0.95.0 Bug

Hi, I use SQLite to drive a racing database since 2012 coded in VB.net. In that time I have always used the sqlite-netFx40-binary-bundle-Win32-2010-x.x.xx.x zip and then used the System.Data.SQLite.dll. I have used this process from version 1.0.81.0 to 1.0.94.0. (Im not sure if there is

[sqlite] List duplication

On Mon, 2015-03-02 at 21:10 -0600, Mike Owens wrote: > The problem is that this is the very bone of contention in the reply-to > religious war. Religious as in there are strongly-held beliefs on both sides, but only one is really based in logic and common sense? :) > Is it not? I may be wrong,

[sqlite] List duplication

On 2015-03-03 01:15 AM, Mike Owens wrote: > For clarity, here is the currently policy for Reply-to as it is set in > Mailman: > > > - Should any existing Reply-To: header found in the original message be > stripped? If so, this will be done regardless of whether an explict >

[sqlite] List duplication

On 2015-03-03 12:42 AM, Darren Duncan wrote: > I think that what needs to be done is for each foo at sqlite.org to > return an error/undeliverable message if someone sends a message to > it, citing that all messages must be explicitly sent to the > corresponding foo at