Hi Ralph,I think in your case this is indeed a nice approach. Given that INTERSECT is not yet supported in Phoenix, you can instead use AND to connect your conditions, which would work almost as efficiently as applying INTERSECT on your inner queries:SELECT * FROM t WHERE pk IN (SELECT pk from t where q1 = ?) AND pk IN (SELECT pk from t where q2 = ?);If your query is of OR logic, unfortunately there is currently no optimal way to do this until we have UNION (https://issues.apache.org/jira/browse/PHOENIX-671) implemented. That is because with OR subqueries, we do a left join instead of a semi join, and left joins do not use PK skip scan according to its semantics. A query similar to the above example with OR logic would only run slower than one simply doing a full-scan on the main data table (without using the index table at all).Thanks,MaryannOn Mon, Jan 19, 2015 at 1:25 PM, Perko, Ralph J <Ralph.Perko@pnnl.gov> wrote:Hi
I have a question about the most efficient way to query many indexed columns. Here is the scenario:
Say I have a table with 100 fields
The first 10 fields are core fields and the client wishes to query them in any combination.
This is too many fields to create a secondary index for every combination so I create just 10, one for each core field.
Then when a query is submitted, I create a single query for each secondary index and return just the PK, thus taking advantage to the index:
Example – for each indexed field included in the query:
SELECT PK FROM Table WHERE <indexed field> = <some value>’;
Then once I have a list of all PK values from all the index queries I will either get the combination or intersection of all PKs, depending on the query logic (and/or)and then run a final select:
SELECT * FROM Table WHERE pk IN(…);
Does this sound like a reasonable approach?