CODEWITHSUNDEEP
sqlmysqlindexingsql-order-byazure-mysql-database
Ruibin Zhang
Ruibin Zhang

Reputation: 1

Does MySQL return results ordered by id when using LIMIT with an increasing id condition?

I’m optimizing a query for better performance, and I use the following SQL statement:

The id condition increases with each iteration to narrow the result set.

EXPLAIN Output

If event_date is not a peak date, the EXPLAIN result is:

1  SIMPLE  my_table  (null)  index_merge  PRIMARY,idx_event_date  idx_event_date,PRIMARY  14,8  (null)  98045  10  Using intersect(idx_event_date,PRIMARY); Using where

However, if event_date is a peak date (e.g., the 28th), the output is:

1  SIMPLE  my_table  (null)  range  PRIMARY,idx_event_date  PRIMARY  8  (null)  52926100  1  Using where

Question

In both cases, will MySQL return the results ordered by id by default, considering that:

  1. event_date has an index.
  2. The id > X condition is used to narrow the result set progressively.
  3. The query does not explicitly include ORDER BY id.

It is very important that my query cover all relevant records. It is only guaranteed if the output is ordered by id.

I compared the result with sql statement:

SELECT * FROM my_table
WHERE status_flag = 0
  AND event_date = '2025-03-25 23:59:59.999'
  AND id > 42006893
order by id
LIMIT 5000;

They have the same result roughly.

As the idx_event_date has a hidden primary key follewd, that is the reason why I believe that the output is orderd by id even without specifying order by id

Upvotes: 0

Views: 60

Answers (1)

SQLpro
SQLpro

Reputation: 5187

There is never an implicit ORDER BY in any RDBMS... The lack of ORDER BY clause in a query leads the RDBMS to give a dataset (rows of the table) without any order, even though it seems that way, and this order can change completely from one run to the next without any warning.

You must remenvber that SQL is not an execution language but a query language. A query language show a pattern of what the user want to have as a result, but never the way to compute the result. Never the SQL code is executed. It is translated into a mathematical formulae (algebraic tree) then this mathematical tree is modified by equivalence for performance reasons, which leads to giving an execution plan of the query, composed of different steps which are execution modules in order to deliver the result. The order of its modules and the nature of the internal algorithms of each of the modules can vary depending on certain circumstances (volume of tables and indexes, distribution of data, availability of resources...)

Upvotes: 0

Related Questions