phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject WHERE clause behavior
Date Sat, 09 Aug 2014 15:46:25 GMT
Hi Faisal,
Yes you can use a built-in Boolean function as you've shown in your query.
You can also omit the =TRUE part like this:

SELECT name
FROM profileTable
WHERE name LIKE 'Ale%' AND *myFunc*(name);

How this is processed depends on whether or not the table is salted and
the name is the leading column in your primary key constraint (if name is
your primary means of accessing rows, then it should be) or the name is
a leading column in a secondary index (if this is a common means of
accessing rows and you do more reads than writes, then it should be). The
short answer is it shouldn't matter (as long as the correct answer is
returned) and is subject to change as we enhance our abilities to do
further optimizations.

The long answer is that if the name is part of the PK, then Phoenix will do
a range scan from 'Ale' to 'Alf' and will filter on the server side the
included rows using your built-in function. Otherwise, it'll do a full
table scan and evaluate first the LIKE expression and if it passes the
built-in function on each row.

Either way, the scan will be chunked up along the PK axis and executed in
parallel on the client side. For more detail, take a look at some of these
presentations: http://phoenix.apache.org/resources.html

Thanks,
James

On Saturday, August 9, 2014, faisal moeen <fmorakzai@gmail.com
<javascript:_e(%7B%7D,'cvml','fmorakzai@gmail.com');>> wrote:

> Hi,
>
> If have a query:
>
> SELECT name
> FROM profileTable
> WHERE name LIKE 'Ale%' AND *myFunc*(name)=TRUE;
>
> I have  two questions:
>
> 1) Can I use *myFunc* in the WHERE clause if my function return boolean.
>
> 2) If yes, how would Phoenix translate it into get/scan request. Will it
> first use a scan to get all names like 'Ale%' and then apply *myFunc* on
> it? Or will it do both requests separately and join the result?
>
> Thanks
>
> --
>
> Regards
> Faisal Moeen
>

Mime
View raw message