Sorting issue with limit and offset MYSQL

Question

I am facing sorting issue in mysql

See the output of below query:

select astrologers.id,astrologers.name,chat_online,online,experience from `astrologers` 
where `astrologers`.`status` = '1' 
order by experience asc limit 10;

id	name	chat_online	online	experience
15	Astro Anoop	0	0	3
20	Test Astro2	0	0	3
3	Test anoop	0	0	5
4	Anoop Kumar trivedi	0	0	5
7	Test	0	0	5
58	Neeraj yadav	1	0	5
45	Satish Kumar Gupta	1	1	10
56	AP Sharma	1	0	15
40	VG Astrologer App	1	0	55

In above result id 58 (Neeraj yadav) is at 6th position but when I run the same query with limit 3, same id 58 (Neeraj yadav) is at 3rd position:

select astrologers.id,astrologers.name,chat_online,online,experience 
from `astrologers` 
where `astrologers`.`status` = '1' 
order by experience asc limit 3;

id	name	chat_online	online	experience
20	Test Astro2	0	0	3
15	Astro Anoop	0	0	3
58	Neeraj yadav	1	0	5

The 3rd row in above result should be id 3 (Test anoop) but it gives id 58 (Neeraj yadav)

Is this bug in mysql?

Answer 1

Is this a bug in MySQL?

No. The problem is that your sort is not deterministic, and gives ties in the third position:

|  3 | Test anoop          |           0 |      0 |          5 |
|  4 | Anoop Kumar trivedi |           0 |      0 |          5 |
|  7 | Test                |           0 |      0 |          5 |
| 58 | Neeraj yadav        |           1 |      0 |          5 |

All 4 users have the same experience, hence leaving the database to figure out how they should be sorted.

When asked to return to top 3 rows, the database picks the first two, and then one of the 4 ties. The result that you get might not be consistent over consequent executions of the same query, as you are starting to see.

Bottom line: know you data; if you want a deterministic result, then use a deterministic sort. We could, for example, use id to break the ties, hence making the result predictable:

order by experience, id limit 3