Question 183697 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Can I get an ORDER BY with several columns but still deterministic?

I know that a deterministic ORDER BY is when you sort by a UNIQUE INDEX column, 1) is this true?.

I want to sort by the columns name and percentage.

There is a possibility that 2 or more students have the same name and the same percentage, therefore, the ORDER BY in that case would be non-deterministic because MySQL would define which one to put first and which one to put after (not me), 2) is this true?

So, my third question is (the main one), if I want to sort by several columns but still have a deterministic order, should I use a UNIQUE INDEX column at the end of all my columns, e.g.

ORDER BY name, percentage, id?

Considering that the id is the primary key

I still don’t know if I understand deterministic and non-deterministic correctly.

Tags:

2

Answers


  1. 0
    1. Not necessarily. If the results don’t contain any duplicates in the ORDER BY columns, the order will be deterministic.
    2. Yes, if there are duplicates the the results are non-deterministic.
    3. Yes, adding a unique column to the ORDER BY makes it deterministic.

    Whether this matters depends on your application.
    It can be important if you’re doing query-based replication and you use INSERT INTO table SELECT .... Without deterministic ordering, you may get inconsistent AUTO_INCREMENT IDs between the master and replicas.

    Login or Signup to reply.
  2. 0

    To answer your questions:

    1. Yes
    2. Yes, but to be more concrete: It’s the quick sort algorithm which mysql uses to sort the data that causes the randomness.
    3. If you need a deterministic query then yes, this is a good way.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search