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
