From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> |
Cc: | PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: query with results and also results size |
Date: | 2010-08-17 22:26:00 |
Message-ID: | AANLkTin1gqTp6dKbnvjd+ZYcPhy1Cd3HpLqw3=FM6nNe@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 17 August 2010 23:08, Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> wrote:
> I query a view based upon a number of user-input criteria. The
> results are displayed to the user 10 rows at a time. In my page view
> for the end user, I want to list "search results, records X through X
> + 9 out of (total)".
>
> Right now every time a person clicks through a page, the page request
> results in two queries. One query gets the count of the total number
> of records that meet the search criteria. The second query gets the
> 10 records for the current page.
>
> I want to reduce the number of round trips to the database, and if
> possible stop from performing the search twice.
>
> I can combine the two queries like this:
> select first.*, second.total_count
> (select * from patient_data_view where (...) order by ... offset _
> limit 10) first
> join
> (select count(id) as "total_count" from patient_data_view where (...))
> second on true;
>
> Doing it that way moves from two trips between the application and the
> database to one, but that one query still has two searches based upon
> the same criteria in it.
>
> I cannot store the count associated with each search between page
> views, because there are other people accessing the system
> concurrently and the number of records meeting the search criteria can
> change.
>
> Is there a way to write the query so that it does not perform the search twice?
>
How about using LIMIT 11? That way you can display 10, but if you
count 11, you can provide a "next" link.
--
Thom Brown
Registered Linux user: #516935
From | Date | Subject | |
---|---|---|---|
Next Message | STA | 2010-08-18 03:39:14 | List of User Defined Types? |
Previous Message | Michael Swierczek | 2010-08-17 22:08:40 | query with results and also results size |