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:

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


On Saturday, August 9, 2014, faisal moeen <fmorakzai@gmail.com> wrote:

If have a query:

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?



Faisal Moeen