Until [MODE-1904] Add support for SQL Count function - JBoss Issue Tracker is finished, any thoughts on an optimized way to count nodes that would result from a JCR-SQL2 query? In order to support paging, I had hoped to query using LIMIT, but run a second query purely for a total count. Rather than removing LIMIT and sublisting in-memory, any other recommendations?
Not really, there is no optimized way to count atm.
Even if/when MODE-1904 is implemented, it will (most likely) only work optimally in a subset of cases - when no ACLs are used. If ACLs are used, there is not way to implement an optimal COUNT function.