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" <simon.james.davies at gmail.com> a ?crit :
> On 3 March 2015 at 11:10, Eric Grange <zarglu at gmail.com> 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 have several dozen millions of rows, and both are accessed > > independently of each others by some queries, their current structure has > > no performance issues for those queries. > > > > However I have a new query which is like > > > > 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, > without > > the A1 condition, there can be millions of resulting rows. > > > > 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. > > Have you tried indexing on A2? > > . > . > . > > Is there a better way that would not involve duplicating the data? > > > > Eric > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
