Re: Design efficient pagination query

Hi

Change the design, does the user really want to know the total number of rows or just that there’s more? Maybe they just care about an estimate of rows returned which could use less filters that are easier to index.

You definitely don’t need to be doing the count on each page. I would also suggest that you’d be better off using a filter on your order by column and a unique key rather than using fetch offset

Hope that helps,
Andy

On Thu, Sep 14, 2023 at 1:29 PM, yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Hello All,
>
> We found as part of UI search query we are getting two queries executed
> each time in the database ,because of pagination implementation at
> application level
>
> 1)Getting the required results out of the query based on input search
> criteria which is showing first 100 rows
>
> 2)Another query with exactly same join and input parameter, but its a
> count() query to see the total number of rows from the query.
>
> The count query is used to show the user , how many pages the results
> spans across. And the "limit 100" query actually fetches the all the
> required fields to show up in the same page. And with each page the user
> scrolls to, these two queries executes one after other for every page , in
> the database for every user. This takes more time and also impacts customer
> experience.
>
> While, we were trying to minimize the result set scan with multiple
> strategies so that the query can give first- N results to the UI user as
> quickly as possible, at the same time the count() query has to scan all the
> data to display the total number of rows information. So wanted to know ,
> if by anyway we can combine these two queries to single query? or say can i
> fetch total record count in the same query which fetches the first 100 rows
> for the display on the screen at one hit to the database? Or should we
> change the design someway ?
>
> Select col3, col4,col5
> from table1, table2
> where table1,col1= table2.col1
> order by transaction_date desc
> limit 100 offset 0;
>
> Select count(*)
> from table1, table2
> where table1,col1= table2.col1;
>
> Regards
> Yudhi
>

--
http://www.freelists.org/webpage/oracle-l