[sqlite] Multi-table index ersatz?

2015-03-04 Thread Hick Gunter
://sdm.lbl.gov/fastbit/ -Urspr?ngliche Nachricht- Von: Eric Grange [mailto:zarglu at gmail.com] Gesendet: Mittwoch, 04. M?rz 2015 08:24 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Multi-table index ersatz? > Rowids will be faster than primary keys. My primary keys are ROW

[sqlite] Multi-table index ersatz?

2015-03-04 Thread Eric Grange
> Rowids will be faster than primary keys. My primary keys are ROWIDs ("INTEGER PRIMARY KEY" actually) None of the index was exploited for the order by, and the matched records in table A are scattered in pages all over the database, so ordering them in memory has a tendency to "replace" the

[sqlite] Multi-table index ersatz?

2015-03-03 Thread Dan Kennedy
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] Multi-table index ersatz?

2015-03-03 Thread Hick Gunter
t gmail.com] Gesendet: Dienstag, 03. M?rz 2015 12:10 An: General Discussion of SQLite Database Betreff: [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 : som

[sqlite] Multi-table index ersatz?

2015-03-03 Thread Clemens Ladisch
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?

2015-03-03 Thread Eric Grange
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] Multi-table index ersatz?

2015-03-03 Thread Simon Slavin
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?

2015-03-03 Thread Eric Grange
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] Multi-table index ersatz?

2015-03-03 Thread Simon Davies
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