use dynamic offset in a query

Question

I have a query

SELECT SQL_CALC_FOUND_ROWS a.memberid, a.category_id, a.content, a.count_cid, a.importance
FROM tb_profilingdata a, tb_member b
WHERE a.memberid = b.memberid AND a.category_id IN ($catstr)  AND a.memberid NOT IN ( $seen_txt) AND b.gender != '$gender'
ORDER BY a.memberid, a.category_id LIMIT $offset, 4500

Since my table is very large, i want to limit my query result to a certain limit. An also choose a dynamic offset, so that i can get random set of values everytime i run the query.

Till now i was calculating random offset based on total number of rows in the table through PHP. But if the offset value is larger than the total number of rows returned by the query, than the result would be empty.

So is there any way through which i don't have to load the entire table as well as set an appropriate random offset so that i can get random values?

score 0 · Answer 1 · edited May 23 '17 at 11:48

Try this nested query:

SELECT c.* FROM (
    SELECT a.memberid, a.category_id, a.content, a.count_cid, a.importance
    FROM tb_profilingdata a, tb_member b
    WHERE a.memberid = b.memberid AND a.category_id IN ($catstr) AND a.memberid NOT IN ($seen_txt) AND b.gender != '$gender'
    ORDER BY RAND() LIMIT 4500
) c ORDER BY c.memberid, c.category_id

Note: you will be unable to get total count of rows in the table using SQL_CALC_FOUND_ROWS.

There exist several ways to optimize the ORDER BY RAND().

score 0 · Answer 2 · answered Feb 02 '13 at 06:20

Use Turnery operator to get the offset from the url Like following (isset($_REQUEST['offset']))?$offset=$_REQUEST['offset']:$offset=0;

for random offset use rand function $offset=rand(0,totalrecord/4500);

and the pass this offset in your query

SELECT SQL_CALC_FOUND_ROWS a.memberid, a.category_id, a.content, a.count_cid, a.importance FROM tb_profilingdata a, tb_member b WHERE a.memberid = b.memberid AND a.category_id IN ($catstr) AND a.memberid NOT IN ( $seen_txt) AND b.gender != '$gender' ORDER BY a.memberid, a.category_id LIMIT $offset, 4500

use dynamic offset in a query

2 Answers2