Questions tagged [pagination]
Separating results into equally-sized 'pages' typically for display purposes. Also known as 'paging'.
70 questions
0
votes
0
answers
30
views
Postgres: How to select rows, grouping by rows with same column field into one row, with pagination
I've a table, with fields like
id
batchId
senderId
recipientId
uuid1
uuid
uuid5
uuid7
uuid2
uuid
uuid5
uuid8
uuid3
uuid2
uuid6
uuid9
I need to select rows with pagination, but handling rows with ...
1
vote
0
answers
34
views
Is it even possible to create a scalable rhyming dictionary for 10 million words in a single language like English?
I'm going in circles brainstorming ideas and TypeScript or SQL code to implement basically a "rhyming database". The goal of the rhyming database is to find rhymes for all words, not just ...
- 221
2
votes
1
answer
269
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 ...
- 197
2
votes
1
answer
520
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 ...
- 23
1
vote
1
answer
264
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
0
votes
1
answer
119
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 (...
- 101
1
vote
1
answer
4k
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 ...
- 217
0
votes
1
answer
704
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 ...
- 169
2
votes
1
answer
223
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
0
votes
1
answer
613
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 (...
- 353
1
vote
1
answer
556
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
5
votes
1
answer
2k
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
14
votes
1
answer
807
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
3
votes
1
answer
5k
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 ...
- 143
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
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.
...
- 151
1
vote
1
answer
223
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
(
...
- 113
0
votes
2
answers
319
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 ...
- 105
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 ...
- 113
1
vote
0
answers
98
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 ...
- 11
2
votes
1
answer
825
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
1
vote
0
answers
186
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)
-> ...
- 121
0
votes
0
answers
130
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 | ...
5
votes
1
answer
8k
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 ...
- 685
1
vote
1
answer
534
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),
...
- 316
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 ...
- 193
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
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
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 ...
- 103
0
votes
0
answers
61
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 ...
- 1
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 ...
- 1,274
1
vote
1
answer
78
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
5
votes
1
answer
12k
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
12
votes
1
answer
23k
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
3
votes
2
answers
179
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
5
votes
1
answer
975
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
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
12
votes
2
answers
14k
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
7
votes
1
answer
7k
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
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
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
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) ...
- 299
1
vote
1
answer
214
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
4
votes
2
answers
202
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
0
votes
2
answers
171
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 ...
- 103
0
votes
2
answers
769
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 ...
- 327
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
2
votes
2
answers
7k
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
0
votes
1
answer
431
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"
...
- 185
0
votes
2
answers
749
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 ...
- 101