Strange results using order by and limit

Question

I'm trying to set up a pagination using SQL. I want 3 results per page and here is what I have done :

SELECT mot_cle.* FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 0; --Page 1

SELECT mot_cle.*  FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 3; --Page 2

SELECT mot_cle.*  FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 6; --Page 3

SELECT mot_cle.* 
FROM mot_cle 
ORDER BY hits DESC LIMIT 3 OFFSET 9; --Page 4

I checked many times and this is not very complicated but my results are not really what I expected :

Page 1 :

+-----+--------+------+
| id  |  mot   | hits |
+-----+--------+------+
|   2 | test   |   46 |
|   1 | blabla |    5 |
| 475 | intro  |    3 |
+-----+--------+------+

Page 2 :

+-----+-------+------+
| id  |  mot  | hits |
+-----+-------+------+
| 478 | vrai  |    1 |
|  26 | ouest |    1 |
|  27 | serie |    1 |
+-----+-------+------+

Page 3 :

+-----+-------+------+
| id  |  mot  | hits |
+-----+-------+------+
|  27 | serie |    1 |
|  26 | ouest |    1 |
| 478 | vrai  |    1 |
+-----+-------+------+

Page 4 :

+-----+-------+------+
| id  |  mot  | hits |
+-----+-------+------+
|  27 | serie |    1 |
|  26 | ouest |    1 |
| 478 | vrai  |    1 |
+-----+-------+------+

As you can see, pages 2, 3 and 4 have the same results... When I fetch the 4 pages in one :

SELECT mot_cle.*  FROM mot_cle 
ORDER BY hits DESC LIMIT 20 OFFSET 0;

Result :

+-----+-------------+------+
| id  |     mot     | hits |
+-----+-------------+------+
|   2 | test        |   46 |
|   1 | blabla      |    5 |
| 475 | intro       |    3 |
|  35 | acteurs     |    1 |
|  36 | milieu      |    1 |
|  37 | industriel  |    1 |
|  38 | plaire      |    1 |
|  39 | grandes     |    1 |
|  40 | ingenieries |    1 |
|  41 | francaises  |    1 |
|  34 | partenaire  |    1 |
|  33 | rthgyjhkj   |    1 |
|  32 | cool        |    1 |
|  31 | super       |    1 |
|  30 | vieux       |    1 |
|  29 | moteur      |    1 |
|  28 | yahoo       |    1 |
|  27 | serie       |    1 |
|  26 | ouest       |    1 |
| 478 | vrai        |    1 |
+-----+-------------+------+

Maybe I'm missing something or sorting results and using limit/offset are not compatible, I don't know what's wrong.

Answer 1

The problem here is that the rows all have a hit count of 1, therefore their position when using ORDER BY hits is non-deterministic. And since you execute a new query each time you access a page, the rows will be "scrambled" anew.

To keep your pages consistent, you could also order by their id:

SELECT mot_cle.* FROM mot_cle ORDER BY hits DESC, id ASC LIMIT 3 OFFSET 0; --Page 1

Answer 2

LIMIT basically "aborts" the query once enough matching rows have been found. E.g. if you have LIMIT 2, then as soon as two rows which match the join/where clauses are found, the rest of the query goes away and you get those two rows.

But if you have an ORDER BY in there, then the ENTIRE matching result set is ordered, then the LIMIT is applied to that sorted set.

e.g. if you have some records that'd be returned as 5,10,203,3,92, then

SELECT id ... LIMIT 2             ->    (5,10),203,3,92
                                        ^^^^^^---actual returned-to-client results
SELECT id ... ORDER BY id LIMIT 2 ->    3,5,10,92,203 -> (3,5),10,92,203
                                           ^^---internal-only results
                                                         ^^^^--actual returned-to-client results.