Questions tagged [pagination]
Separating results into equally-sized 'pages' typically for display purposes. Also known as 'paging'.
64
questions
0
votes
1
answer
289
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 ...
- 207
0
votes
1
answer
180
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 ...
- 157
2
votes
1
answer
155
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
298
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
266
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 ...
- 11
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
14
votes
1
answer
752
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
2
votes
1
answer
3k
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
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 ...
- 358
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
1
vote
1
answer
187
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
0
votes
2
answers
229
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
93
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
737
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
166
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
105
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 | ...
4
votes
1
answer
6k
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
1
vote
1
answer
464
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
2
votes
1
answer
2k
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
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
6
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 ...
- 175
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
53
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
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,145
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
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
12
votes
1
answer
20k
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
2
votes
2
answers
161
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 ...
- 515
5
votes
1
answer
885
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 ...
- 515
12
votes
2
answers
12k
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
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
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
4k
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
1
vote
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) ...
- 249
1
vote
1
answer
156
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
189
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
121
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
744
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 ...
- 317
1
vote
1
answer
960
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
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 ...
- 181
0
votes
1
answer
397
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"
...
- 175
0
votes
2
answers
727
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
2
votes
1
answer
5k
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
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
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
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
2
answers
902
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
8
votes
1
answer
13k
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