Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [pagination]

Separating results into equally-sized 'pages' typically for display purposes. Also known as 'paging'.

Filter by
Sorted by
Tagged with
2 votes
1 answer
104 views

Emulate Loose Index Scan for multiple columns with alternating sort direction

A while back I asked this question about efficiently selecting unique permutations of columns in Postgres. Now I have a follow-up question regarding how to do so, with the addition of being able to ...
hunter's user avatar
  • 197
2 votes
1 answer
103 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 ...
odyssey's user avatar
  • 23
1 vote
1 answer
48 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 ...
Gummi's user avatar
  • 13
0 votes
1 answer
67 views

Optimized count for large table using triggers, views, or external cache

I have a public API method that calls a Postgres (14) database and returns a paginated list of rows belonging to a user along with a total count and page index. The count is very costly to perform (...
JackMahoney's user avatar
0 votes
1 answer
2k views

Optimal way to get a total count of rows in a paged query in Postgres?

I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing ...
jcollum's user avatar
  • 207
0 votes
1 answer
411 views

Can you make Postgres execute ORDER BY after OFFSET and LIMIT?

Consider this pagination scenario id name last_name 1 Mickey Mouse 2 Donald Duck 3 Scrooge McDuck 4 Minerva Mouse 5 Goofus Dawg 6 Daisy Duck SELECT * FROM users ORDER BY id DESC LIMIT 3; The ...
Sergey Zolotarev's user avatar
2 votes
1 answer
180 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 ...
DanMossa's user avatar
  • 145
0 votes
1 answer
488 views

Best way of SELECT on large table with filter to filter out around 1m rows with indexes

I have a table billing_billcycleorders that contains a foreign key billing_cycle_id. There are around 0.9m records for a particular billing_cycle_id. I want to select the data in the chunks for ~5000 (...
sirajalam049's user avatar
1 vote
1 answer
393 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 ...
hskris's user avatar
  • 111
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 ...
Daniel's user avatar
  • 51
14 votes
1 answer
773 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 ...
Twicetimes's user avatar
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 ...
Tobi's user avatar
  • 133
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 ...
Skary's user avatar
  • 368
3 votes
1 answer
2k 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. ...
Dogukan Evcil's user avatar
1 vote
1 answer
195 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 ( ...
deadwards's user avatar
  • 113
0 votes
2 answers
268 views

Limit and offset (pagination) for rows with related data (with joins)

I have tables Alpha and Beta. Beta belongs to Alpha. create table Alpha ( id int auto_increment primary key ); create table Beta ( id int auto_increment primary key, alphaId ...
Ruslan Plastun's user avatar
1 vote
2 answers
2k views

MySQL: Data pagination according data on another table

I have a database with the following structure Items table: itemID, itemName Ads table: AdsId, itemID I would like to make pagination on items table, 10 items in each page, but first I must retrieve ...
Ali A. Jalil's user avatar
1 vote
0 answers
95 views

MySQL pagination

MySQL - I am trying to fix this pagination issue on my servers . The RAM is 40 GB and the innodb_buffer_pool_size is set to 36 GB which is a little more than 80%. Still getting paging alerts. Any ...
MySQLLearner's user avatar
2 votes
1 answer
770 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 ...
Knox's user avatar
  • 123
1 vote
0 answers
177 views

Reduce Count(*) time for many records [closed]

Query: EXPLAIN ANALYZE select count(*) from product; ROWS: 534965 EXPLANATION : Finalize Aggregate (cost=53840.85..53840.86 rows=1 width=8) (actual time=5014.774..5014.774 rows=1 loops=1) -> ...
Adnan Mohib's user avatar
0 votes
0 answers
119 views

Adding multiple indexes to avoid skip/limit in MongoDB

Let's say I have a MongoDB's database with millon of records having a City name, a User name and an activity. City | User | Action ------------+--------+------------------- Gotham | ...
Mauro H. Leggieri's user avatar
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 ...
Iman Tumorang's user avatar
1 vote
1 answer
513 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), ...
PirateApp's user avatar
  • 306
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 ...
CodeBreaker's user avatar
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 ...
Anant's user avatar
  • 131
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 ...
Arne Claassen's user avatar
0 votes
1 answer
1k views

In Oracle 12c, how can we implement pagination on large table using rowid?

I am new to the database. I would like to implement pagination on large tables using rowid but what I found that rowid can be alphanumeric digits and not necessarily they are in any order so I wonder ...
Ashish Pancholi's user avatar
0 votes
0 answers
57 views

Mongo - Find is returning differents results with each request

Currently in my collection has the following spec: Version: MongoDb - 4.0.6 Sharding: 3 shards Shard key: Publication identifier The shards are consistent The problem is due using the same operation ...
Ainokila's user avatar
1 vote
2 answers
2k 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 ...
Gajus's user avatar
  • 1,195
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 (...
julealgon's user avatar
  • 129
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'...
Saroj Shrestha's user avatar
12 votes
1 answer
22k 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 ...
David's user avatar
  • 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 ...
rubik's user avatar
  • 525
5 votes
1 answer
929 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 ...
Florian Klein's user avatar
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 ...
rubik's user avatar
  • 525
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 ...
Wonka's user avatar
  • 145
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 ...
Jeff Camera's user avatar
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' ...
kswen's user avatar
  • 23
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 * ...
Svn342's user avatar
  • 41
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) ...
j10's user avatar
  • 289
1 vote
1 answer
185 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 ...
Gary's user avatar
  • 562
4 votes
2 answers
193 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 ...
Khalil's user avatar
  • 143
0 votes
2 answers
147 views

Is it possible to keep track of which rows were selected and fetched last in MySQL?

I am working on a small website using Java where the user posts are stored in MySQL, I am getting the posts 5 at a time (5 rows using LIMIT 5) by most recent one (using timestamp DESC) and send it to ...
aero's user avatar
  • 103
0 votes
2 answers
757 views

SQL Server target/total server memory is not equal to sum of database size in dm_os_buffer_descriptors

I am having an issue in my SQL Server like high memory paging rate and more compilation/sec which lead me to a different observation in memory, so first i checked what is total size of memory of ...
info.sqldba's user avatar
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 ...
Matt Mc's user avatar
  • 113
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 ...
Patrick B.'s user avatar
0 votes
1 answer
418 views

left outer join with order by on foreign key returns duplicates with pagination

I have two tables api_user and api_user with api_user having foreign key to user table. The schemas for the two tables are as listed below. Table "public.api_user" ...
Divick's user avatar
  • 185
0 votes
2 answers
733 views

Oracle - Count groups in group by

I have the following query: SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*) "Count" FROM "MyTable" WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13) GROUP BY ...
der_chirurg's user avatar
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 ...
Jordan0Day's user avatar
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 '...
Murad Yusufov's user avatar