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 created > '<last element created timestamp>' ORDER BY created ASC LIMIT 20;
If it is a numerical primary key
SELECT * FROM users WHERE id > 100 ORDER BY id ASC LIMIT 20;
But what can one do when the table is ordered by non unique text column (first_name
for example)?
First thing that comes to mind is the OFFSET
method, but there are performance penalties on large tables.
Alternatively, one could use a cursor, but the internet agrees that it not recommended way on a public web application.
Best Answer
One solution that performs well would be being smart with indexes.
First, create an index on two columns, one being your non unique text column and a second one being your id column (it can be any column that can be differentiated between non unique text column rows):
NOTE: the order of columns does matter. Use the same order that is used in the
select
queryThen use this query to get x page.