Questions tagged [pagination]
Separating results into equally-sized 'pages' typically for display purposes. Also known as 'paging'.
67
questions
19
votes
3
answers
6k
views
Pagination in SQL Server
I have a very large database, roughly 100 GB. I'm executing query:
select * from <table_name>;
and I want to show only the 100th to 200th rows.
I want to understand how this happens ...
- 301
19
votes
2
answers
5k
views
Paging performance with customizable sorting over many millions of rows
In our application we have a grid where users can page over a large number of records (10-20 million). The grid supports sorting in ascending and descending order in a number of columns (20+). Many of ...
- 920
14
votes
1
answer
772
views
Why am I seeing key lookups for all rows read, instead of all rows matching the where clause?
I have a table such as the following:
create table [Thing]
(
[Id] int constraint [PK_Thing_Id] primary key,
[Status] nvarchar(20),
[Timestamp] datetime2,
[Foo] nvarchar(100)
)
with a ...
- 263
12
votes
2
answers
13k
views
MySQL - UUID/created_at cursor based pagination?
For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
- 145
12
votes
1
answer
21k
views
Efficient pagination for big tables
Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results.
In an attempt to not use OFFSET, I pass the id from the last row in ...
- 123
10
votes
2
answers
267
views
Windowing offset based on time stamp
I'm writing a query that will be used to page results for a social feed. The concept is the mobile app will request N items, and provide a starting datetime which I have called @CutoffTime below. ...
- 4,771
8
votes
3
answers
754
views
Most cost efficient way to page through a poorly ordered table?
I have a table that has three columns:
HashUID1, HashUID2, Address_Name (which is a textual email address, and the previous two hash colunms are of some crazy creation to link event participant tables ...
- 1,251
8
votes
1
answer
14k
views
OFFSET FETCH in SQL Server 2008
I have this query to paginate the results and it was working fine on SQL Server 2012. However I had to move my database to SQL Server 2008 and now my stored procedure is not working. I did some ...
- 239
7
votes
3
answers
2k
views
Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N
We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations.
The problem generally ...
- 185
7
votes
1
answer
6k
views
What is the recommended way to join junction tables for efficient ordering/pagination?
Summary: I have a simple database schema but even with just a few 10's of thousands of records the performance on basic queries is already becoming a problem.
Database: PostgreSQL 9.6
Simplified ...
- 173
6
votes
1
answer
2k
views
SQL Server table query with pagination performance tuning, understand the current solution
as stated in the title i start a performance tuning of a table query with pagination generated by a legacy program that use Linq To SQL as ORM.
I have found this resource in which is highly ...
- 368
5
votes
1
answer
11k
views
Reduce query time for higher offset in sql server
Currently, I have table base_voter with data around 100M of dummy data. I have stored procedure as follows:
CREATE Procedure [dbo].[spTestingBaseVoter]
@SortColumn NVARCHAR(128) = N'...
- 195
5
votes
1
answer
1k
views
Inconsistent keyset pagination when using (timestamp, uuid) fields
I am using the keyset pagination method for uuids on my Postgres database described in this post:
How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data?
However, I have ...
- 51
5
votes
1
answer
922
views
How to optimize a keyset pagination query with CTEs on a big table?
I tried to document myself as much as I could on the topic before coming here to bother you, but here I am anyway.
We want to implement keyset pagination on this table:
create table api.subscription ...
- 153
4
votes
2
answers
191
views
Implicit conversion in fetch
While studying different query plans to improve performance I noticed that FETCH is doing an implicit convert to bigint.
Example table and query:
CREATE TABLE checkPagintion
(
Id INT NOT NULL ...
- 143
4
votes
1
answer
3k
views
Paging in SQL Server: Is an order by clause required? (currently, rows are being missed)
We have some code that pages through a SQL result. (Currently running on SQL Server 2008)
We notice that when paging is done, some rows are not returned. Let me clarify this a bit:
nHibernate ...
4
votes
1
answer
7k
views
How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data?
Context:
Out of curiosity, I'm doing load testing for my application. And then the result there's a lot of concurrent inserts happened.
After doing the load testing on create-endpoint, I'm trying ...
- 635
4
votes
1
answer
5k
views
MSSQL efficient paging including a join on big tables
We have a table with products and a table with a current selection/assortment of products. Now need to join them create an export file by some .net code.
Currently using query like this:
SELECT * ...
- 41
4
votes
1
answer
439
views
Pagination - Text comparison with greater than and less than with DESC
I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if ...
- 745
3
votes
1
answer
846
views
Paging in SQL Server 2012 vs. previous versions
I have used lot of paging code in SQL Server 2008. The format is
With CTE(
-- SQL With RowNumber
)
-- SELECT ALL Page N
This is working fine. But I have heard that SQL Server 2012 has better ...
3
votes
1
answer
1k
views
Infinite scrolling with full text search in PostgreSQL
I am building a service that allows full-text searches and I would like to paginate the results.
I don't want to use OFFSET, so I opted for infinite scrolling with the keyset pagination, as outlined ...
- 525
3
votes
1
answer
2k
views
What are the methods to paginate a table 100 rows at a time?
Let's say we have a table with a large number of records. Now to show all the data using pagination in a basic framework using a MySQL query, we can use limits to get a subset of rows:
SELECT * FROM ...
- 131
2
votes
1
answer
6k
views
In MySQL, what's the most efficient way to query an entire table in chunks?
If I have a table User is it faster to do something like:
SELECT * FROM user WHERE id > ? ORDER BY id LIMIT 10000
(With each subsequent query using the previous last row's id as the param)
Or to ...
- 123
2
votes
2
answers
6k
views
UPDATE with LIMIT / OFFSET in PL/pgSQL function
In my Postgres 9.2 database I've got the following SELECT, which will be used by a PL/pgSQL function:
SELECT
id
FROM
tablea
WHERE
migrated = false;
I want to get 2000 records each ...
- 301
2
votes
1
answer
3k
views
Pagination while sorting a non unique text column
What are the ways to paginate a table on a non unique text column?
On a unique timestamp column, one can create an index on it and execute the following to move forward
SELECT * FROM users WHERE ...
- 183
2
votes
1
answer
1k
views
Oracle pagination with order by field not unique performance
Usually, we will write pagination SQL like this if order by field is unique:
SELECT *
FROM (
SELECT XX.*, ROWNUM AS RN
FROM (
SELECT *
FROM T_LOG
WHERE OP_TYPE = 'Q' ...
- 23
2
votes
1
answer
170
views
How can I paginate when ordering by `date_bin`?
I have the following query
SELECT u.update_time, about_me
FROM users u
ORDER BY date_bin('14 days', u.update_time, '2023-04-07 23:11:56.471560Z') DESC, LENGTH(u.about_me) DESC, u.user_id;
I get the ...
- 145
2
votes
1
answer
4k
views
Can you paginate through 2 tables in parallel, with 1 query
I want to paginate through 2 different sources.
I think I can do this using the thing where u join 2 queries into one, however, I don't want to merge them in parallel.
Is it possible to select the ...
- 133
2
votes
1
answer
1k
views
Tuple comparison with ORACLE and SQLSERVER
I am working on cursor based pagination using multiple columns. I need to make a tuple comparison since date might not be a unique value.
When I am using MySQL and PostgreSQL, I can do it like this.
...
- 141
2
votes
1
answer
2k
views
Optimistic Paging and separate query for COUNT?
I am using SQL Server 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a total count.
There are 2 ways to achieve this:
Paging ...
2
votes
1
answer
5k
views
Cursor Based Pagination for Timestamp field with non-unique values
We are trying to create a query for the following table :
CREATE TABLE `Action` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`vendor_Id` int(10) unsigned DEFAULT NULL,
`name` varchar(60) ...
- 289
2
votes
2
answers
929
views
How to sort the data fetched from horizontal partitioned tables
I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be ...
- 146
2
votes
1
answer
169
views
Paged query against timestamp populated by trigger missing items
We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out.
First some background. We have a "tombstone" table that is populated using a ...
- 141
2
votes
1
answer
764
views
PostgreSQL Query with pagination is slow when using Merge Join
I am experimenting with trying to optimize a query that's used to iterate over pages of rows with a joined table. After changing the query from using OFFSET to WHERE id > last_id_from_prev_page and ...
- 123
2
votes
2
answers
172
views
Comparison operators give wrong results with keyset pagination
I implemented keyset pagination on these tables
CREATE TABLE profiles (
id serial primary key,
username varchar(255),
followers bigint,
tsv tsvector
);
CREATE TABLE ...
- 525
2
votes
2
answers
1k
views
Paging through data that is frequently changing
When users are browsing pages of data where the data keeps changing, what is the best way to control this so the user experience is not impacted?
If I browse a catalog of 100 records with 10 per page,...
- 254
2
votes
1
answer
2k
views
HQL pagination returns same result on different pages - with SQL
We have the following query in the server (using sql):
Query query = this.entityManager.createQuery(
"SELECT type, id, date, amount FROM Transaction ORDER BY type ASC");
The type field is ...
- 121
1
vote
1
answer
509
views
How to prevent seek pagination from doing sequential scan over entire table
I have 3 tables and a materialized view:
resource_categories contains all category names and metadata
create table if not exists resource_categories (
category_id INT,
title VARCHAR(255),
...
- 296
1
vote
2
answers
1k
views
How to get N rows after the first row matching a condition?
I am implementing a cursor based pagination.
The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the ...
- 1,185
1
vote
1
answer
570
views
Can this query be optimized further?
I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to ...
- 243
1
vote
1
answer
1k
views
Paginated SqlServer Query with Changing Data
The traditional way to do server-side pagination of queries with SqlServer (earlier than 2012) has been to use ROW_NUMBER(), as somewhat covered here and in other places.
Let's say that I'm building ...
- 113
1
vote
1
answer
2k
views
Mysql pagination without offset
Let's assume I have got a table with these data:
I want to order it by createdAt column DESC, and id column DESC
select id, createdAt from post order by createdAt desc, id desc
Now it looks like ...
- 113
1
vote
1
answer
52
views
Efficient Pagination In Postgresql (Keyset Pagination)
I'm working on implementing pagination for a PostgreSQL database in which I have a table that stores users for my application. I have a query that is intended to fetch the next page of users based on ...
- 13
1
vote
1
answer
193
views
Class Table Inheritance combined with running searches and paging
Assume we have tables similar to the following:
CREATE TABLE Base
(
ID int,
Type varchar(20),
Col1 varchar(255),
Col2 varchar(255),
AddedAt datetime2
);
CREATE TABLE Sub1
(
...
- 61
1
vote
1
answer
73
views
Avoiding code duplication for counting and selecting the same resultset
I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (...
- 129
1
vote
1
answer
178
views
Always return X rows with variable starting row
My goal is to return X rows from a table, where the value of X is some value in the low 100's. For the purposes of testing this query, I am experimenting with different X values.
This is a take on ...
- 562
1
vote
1
answer
1k
views
Efficient pagination for big amount of data [closed]
I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '...
- 113
1
vote
1
answer
1k
views
Paging and grouping using CTE?
Given the following code, from the answer to this question, how can I adapt it for paging? I've read that Common Table Expression is a good way to do paging, but since I implemented CTE to solve a ...
- 339
1
vote
1
answer
39
views
Prevent duplicated data when paginated through records that has the same value in MySQL
I have a MySQL database for example,
ID
Title
Purchase_At
1
Title A
2023-12-01
2
Title B
2023-08-22
3
Title C
2023-12-01
4
Title D
2023-08-23
5
Title E
2023-12-01
6
Title F
2023-06-22
7
Title ...
- 13
1
vote
1
answer
368
views
effectiveness of creating index for ORDER BY column when used with many WHERE clauses
Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor ...
- 111