How does mysql order rows with the same value?

Question

In my database I have some records where I am sorting by a column that contains identical values:

| col1 | timestamp              |
| row1 | 2011-07-01 00:00:00    |
| row2 | 2011-07-01 00:00:00    |
| row3 | 2011-07-01 00:00:00    |

SELECT ... ORDER BY timestamp

It looks like the result is in random order. Is the random order consistent? I have these data in two mysql servers can I expect the same result？

Atween

Answer 1

I'd advise against making that assumption. In standard SQL, anything not required by an explicit ORDER BY clause is implementation dependent.

I can't speak for MySQL, but on e.g. SQL Server, the output order for rows that are "equal" so far as the ORDER BY is concerned may vary every time the query is run - and could be influenced by practically anything (e.g. patch/service pack level of the server, workload, which pages are currently in the buffer pool, etc).

So if you need a specific order, the best thing you can do (both to guarantee it, and to document your query for future maintainers) is explicitly request the ordering you want.

Ballenger

+1 note: look for another column you can order by as a second argument to get consistent results – Lytton

The order that rows are returned in is guaranteed ONLY by ORDER BY clause (or in MySQL, an ORDER BY implicitly specified in the GROUP BY clause.) Apart from that, there is NO GUARANTEE of the order rows will be returned in. Apart from that, MySQL is free to return the rows in any sequence. – Platitudinize

Answer 2

Lot's of answers already, but the bottom line answer is NO.

If you want rows returned in a particular sequence, consistently, then specify that in an ORDER BY. Without that, there absolutely NO GUARANTEE what order rows will be returned in.

I think what you may be missing is that there can be multiple expressions listed in the ORDER BY clause. And you can include expressions that are not in the SELECT list.

In your case, for example, you could use ORDER BY timestamp, id.

(Or some other columns or expressions.)

That will order the rows first on timestamp, and then any rows that have the same value for timestamp will be ordered by id, or whatever the next expression in this list is.

Platitudinize

Answer 3

The answer is: No, the order won't be consistent. I faced the same issue and solved it by adding another column to the order section. Be sure that this column is unique for each record like 'ID' or whatever it is.

In this case, you must add the 'ID' field to your table which is unique for each record. You can assign it 'AI' (auto increment) so that you are not going to deal with the maintenance.

After adding the 'ID' column, update the last part of your query like:

SELECT mt.*
FROM my_table mt
ORDER BY mt.timestamp ASC, mt.id DESC

Mcghee

Although not very important if ID is numeric I suspect using "mt.id" in "ASC" order would be a sligthly better fit. Usually the ID increments overtime and that way the resulting order will be "closer" to the insertion order. – Conroy

Makes sense. Depends on the needs. – Mcghee

Answer 4

In ORDER BY condition if the rows are same values or if you want to arrange the data by selecting ORDER BY statement. CASE : You want to ORDER BY the values of column are frequency of words. And two words in the table may have the same frequency value in the frequency occurrence column.. So in the frequency column you will have two same frequencies of two different words. So, in "select * from database_name ORDER BY frequency" you may find any of one the two words having the same frequency showing up just before its latter. And in second run the other word which was showing after the first word showing up earlier now. It depends on buffer memory,pages being in and out at the moment etc..

Covarrubias

Answer 5

According to https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic.

Beginning with MySQL 8.0.21, it is possible to turn off this optimization by setting the optimizer_switch system variable's prefer_ordering_index flag to off.

Civil

Answer 6

That depends on storage engine used. In MyISAM they'll be ordered in natural order (i.e. in order they're stored on the disk - which can be changed using ALTER TABLE ... ORDER BY command). In InnoDB they'll be ordered by PK. Other engines can have their own rules.

Qr

InnoDB always has a PK. If you didn't create one explicitly, InnoDB created it's own 6 byte PK. – Qr

so the the random order is not consistent,because the PK maybe different? – Atween

That's right. Which is yet another reason to have explicit PK. – Qr

Actually, if the query is satisfied from a covering index, then the rows may be returned in order from that index. This is also true if the ORDER BY can be satisfied from the leading columns of an index, avoiding a "Using filesort" operation. The order rows are returned is not "random", and is likely going to be observed as being consistent, because the data and index blocks haven't changed, and MySQL is generating the same execution plan. – Platitudinize