Re: name search query speed

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
=:->

>>John
>>=:->
>>
>>
>>