phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maryann Xue <maryann....@gmail.com>
Subject Re: indexed query question
Date Mon, 19 Jan 2015 19:34:53 GMT
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,
Maryann


On 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
>
>  Table {f1,f2,f3,f4,…f100}
>
>  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?
>
>  Thanks,
> Ralph
>
>

Mime
View raw message