How to do pagination with randomly choose rows from MySQL?

Question

I need to choose rows from a table randomly by certain percentage. As I am using MySQL, I know how to randomly choose records, As I can see from this question: how to randomly select rows in SQL, but there is still one thing missing: If I have 10000 rows, and the percentage is 10%, I will choose 1000 rows from db. How can I do pagination with those rows?

Answer 1

You can't paginate via separate requests by adding simple "LIMIT X,Y" if the sorting is RAND().

1) Simple approach: single user

A simple approach could be to add a new field (integer) to your rows, then you randomly assign a number to each, use that column to sort and then apply LIMIT X,Y to extract paginated results. The obvious limitation here is that there will be only 1 sorting order for all the rows. Eg, if you need to have several users extracting different results from that table, it will not work.

2) More complex approach: multi-user

If you need to have several users working this database under these constraints, then every time a user requests a new "percentage" from these rows, you would need to "save" the selected rows in the database. You would need to add two tables, so that each "percentage request" made is saved and can be browsed independently. Eg:

CREATE TABLE percentage_request (
request_id int AUTO_INCREMENT,
percentage_requested int
);

CREATE TABLE percentage_request_item (
request_id int,
row_id int, /* foreign key pointing to a row */
order_index int /* create your own order index here */
);

Every time a user requests a new percentage of rows from the table, you would:

• randomly select the rows from the main table
• create a new record in table percentage_request to keep track of that user request
• insert X number of records in table percentage_request_item pointing to each selected rows in that specific user request
• then you can serve the requested percentage, page by page via SQL queries like below:

Eg:

SELECT * 
FROM percentage_request_item
INNER JOIN table_rows ON table_rows.id = percentage_request_item.row_id
WHERE request_id = $user_request_id
ORDER BY order_index ASC
LIMIT 100 OFFSET 0

Hope that makes sense!