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. 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
