Mysql Paging With multiple ORDER BY

Question

I have some issue with mysql pager.

• Count all rows is 164.
• pro_type - can be in 3 different integer values;
• pro - integer value;
• cr_date - some date.

Here is my queries:

SELECT id FROM table WHERE f1='1' AND f2='0' AND uid=43 ORDER BY pro_type DESC, pro DESC, cr_date DESC LIMIT 100;

SELECT id FROM table WHERE f1='1' AND f2='0' AND uid=43 ORDER BY pro_type DESC, pro DESC, cr_date DESC LIMIT 100 OFFSET 100;

• Second query result has duplicate rows from first query result.
• Both results do not contain one or few rows.
• The number of rows of the first query 100 and second is 64, so result count is ok.
• Query with limit 200 return all valid rows(164 not duplicated).

Maybe someone knows, what is the problem with ORDER BY & LIMIT OFFSET in that case?

Thank you.

Answer 1

Basically, what is likely to be happening is that the data has duplicate values of pro_type, pro, cr_date. When you use order by and the keys are the same, MySQL does not guarantee the ordering of results with the same key values. In other words, the sort is not "stable".

Assuming that id is unique, the solution is simply to add it to the order by clause so the sort keys are unique:

ORDER BY pro_type DESC, pro DESC, cr_date DESC, ID
------------------------------------------------^
LIMIT 100;