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 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. > > I can make the query instantaneous by duplicating the A1 & B1 fields in a > dedicated C table (along with the primary keys of A & B), index that table, > and then join back the A & B table to get the other fields. > > However this results in a fairly large table of duplicated data, whose sole > purpose is to allow the creation of a fairly large index, which gets me the > performance.
You might be able to make the new table a WITHOUT ROWID table and set its PRIMARY KEY up with the same (or a superset of the) fields of your "fairly large index" in order to save a bit of space. > > Note that if the fields A1 & B1 are removed from their tables and kept only > in C, this has massive performance implication on other queries running > only against A & B, as those fields are leveraged in other composite > indexes. > > Is there a better way that would not involve duplicating the data? > > Eric > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
