Jeremiah Jahn wrote:
On Thu, 2005-03-03 at 11:46 -0600, John A Meinel wrote:
Not really, about 2% of the returned rows are thrown away for security reasons based on the current user, security groups they belong to and different flags in the data itself. So the count for this is generated on the fly needed for pagination in the app which expresses the total number of finds, but only displays 40 of them. If any one knows a way to determine the total number of matches without needing to iterate through them using jdbc, I'm all ears as this would save me huge amounts of time and limit/offset would become an option.
Well, what is wrong with "select count(*) from <the query I would have done>"? Are you saying 2% are thrown away, or only 2% are kept? Is this being done at the client side? Is there a way to incorporate the security info into the database, so that the query actually only returns the rows you care about? That seems like it would be a decent way to speed it up, if you can restrict the number of rows that it needs to look at.
There are other alternatives, such as materialized views, or temp tables, where you select into the temp table the rows that the user would request, and then you generate limit/offset from that. The first query would be a little slow, since it would get all the rows, but all subsequent accesses for that user could be really fast.
The other possibility is to do "limit 200", and then in your list of pages, you could have: 1, 2, 3, 4, 5, ... This means that you don't have to worry about getting 10,000 entries, which probably isn't really useful for the user anyway, and you can still break things into 40 entry pages, just 200 entries at a time. John =:->
Description: OpenPGP digital signature