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 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. According to your numbers, the index on A1 is more important, which implies that the sorting must be done without the help of an index. > 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. > [...] > Is there a better way that would not involve duplicating the data? An index _is_ somthing like a table containing duplicated data; the difference is that it is maintained automatically. You can get the same effect with triggers to maintain the C table. SQLite does not have multi-table indexes. It might be possible to write a virtual table module that does the same as your index on C, but with C being a view. Regards, Clemens
