Questions tagged [pagination]

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

Filter by
Sorted by
Tagged with
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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:...
user avatar
  • 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. ...
user avatar
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 ( ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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) -> ...
user avatar
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 | ...
user avatar
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 ...
user avatar
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), ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 (...
user avatar
  • 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'...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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' ...
user avatar
  • 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 * ...
user avatar
  • 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) ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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" ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 '...
user avatar
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 ...
user avatar
  • 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,...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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. ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 127