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;

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;

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

Is this bug in mysql?

CodePudding user response：

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