On Fri, Apr 08, 2005 at 09:29:13AM -0400, Joel Fradkin wrote:
> My understanding was this gets slower as you move further into the data, but
> we have several options to modify the search, and I do not believe our
> clients will page very far intro a dataset.
>
It gets slower because when you do an offset of 50, you have to pass
through the first 50 before picking up the ones you want. If you
offset 100, you scan through the first 100. &c. If you don't want
to pay that, you need to use a cursor, but this causes the problem
that you have to keep your cursor open across page views, which is a
tricky issue on the Web.
> Currently it returns all records with a count and a display of the records
> your viewing like 1-50 of 470, next page is 51-100 etc.
>
> Is there a fast way to get the count?
Not really, no. You have to perform a count() to get it, which is
possibly expensive. One way to do it, though, is to do
SELECT count(*) FROM tablename WHERE condition LIMIT n;
or something like that. Assuming the condition is reasonably limited
(i.e. it's not going to cost you a fortune to run this), you'll get
the right number back if the number is < n or else you'll get
n. If you have n, your application can say "viewing 1-50 of at least
n records". This is something you see from time to time in this sort
of application.
> getting heat that my search is now case sensitive. What is the best way to
> get a case insensitive search? I could use ~* or perhaps do an
> UPPER(firstname) in the select etc?
The upper() (or lower() -- whatever) stragegy is what I'd use. In
any case, you want to make sure you put functional indexes on all
such columns, because otherwise you'll never get an index scan.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism. --Brad Holland