Questions tagged [pagination]
Separating results into equally-sized 'pages' typically for display purposes. Also known as 'paging'.
58
questions
14
votes
1
answer
683
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 ...
- 243
1
vote
1
answer
78
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 ...
- 123
6
votes
1
answer
572
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 ...
- 287
0
votes
0
answers
62
views
I want pagination in oracle stored procedure
I want pagination in oracle stored procedure in which I will give page start index, page size as input parameter and using offset for skip rows. My query for getting data is a string query of varchar2:...
- 3
2
votes
1
answer
263
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.
...
1
vote
1
answer
94
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
54
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
769
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
28
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
222
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
73
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
38
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 | ...
3
votes
1
answer
2k
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 ...
- 465
1
vote
1
answer
164
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),
...
- 246
0
votes
1
answer
975
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 ...
- 143
6
votes
3
answers
892
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
779
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
46
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
424
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 ...
- 866
1
vote
1
answer
61
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
6k
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
10
votes
1
answer
14k
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 ...
- 103
2
votes
2
answers
124
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
686
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 ...
- 151
3
votes
1
answer
929
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
8
votes
2
answers
7k
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 ...
- 105
6
votes
1
answer
4k
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 ...
- 163
2
votes
1
answer
735
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
3k
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) ...
- 209
1
vote
1
answer
69
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 ...
- 542
4
votes
2
answers
164
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
43
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
632
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
621
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
2
answers
4k
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 ...
- 161
0
votes
1
answer
303
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"
...
- 165
0
votes
2
answers
688
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
4k
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
932
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
17
votes
3
answers
5k
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 ...
- 281
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,...
- 244
2
votes
2
answers
689
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
12k
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
2
votes
1
answer
132
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
10
votes
2
answers
256
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,722
2
votes
1
answer
1k
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
4
votes
1
answer
298
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 ...
- 725
19
votes
2
answers
4k
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 ...
- 910
0
votes
1
answer
2k
views
Pagination, check number of selected records
I am working on pagination for a complex join query and unfortunately I am not sure what's the best way to go... Right now I am thinking about the following solution.
Imagine that SELECT id... is the ...
- 127