0

When we show a table with many thousands of records, we only show a small part of the total result set so as not to send too much data to the client at once. Clients can request more parts of the result set through pagination.

This is how a typical query for such a table looks:

SELECT `expression1`, `expression2` FROM table (JOINS) ORDER BY table.id LIMIT 0,100

The LIMIT clause restricts the result set so that the table does not become too big. The ORDER BY clause enforces a deterministic order.

I have noticed that these queries can become very slow when several database tables are joined and at least one of them contains a large number of rows. Leaving out the ORDER BY clause greatly improves the speed, up to 10000%. Apart from not having to sort, I assume the query optimizes by executing the LIMIT clause before the SELECT clause.

Ironically, it is not necessary that the result set is ordered by id. We just need a deterministic order to facilitate reliable pagination, for example one that follows the order of the primary key or a predetermined index.

Is there an instruction to achieve deterministic order without the ORDER BY clause?

5
  • 2
    1. How many joins are "several?" Do you really need them all? Oct 21, 2020 at 18:30
  • 3
    2. Are all of your joins backed up by indices? Oct 21, 2020 at 18:32
  • 4
    3. Is what you're ordering on backed up by an index? Oct 21, 2020 at 18:36
  • yes, yes, yes.. Oct 21, 2020 at 20:35
  • Of course leaving out the order by makes the query faster. You can either redesign your application to not need the ordering (for example by running a query without a limit and then processing rows in a streaming fashion rather than fetching everything before starting processing (usually not applicable to pagination, but often a useful approach)), or keep ordering and optimize the rest of the query and DB settings.
    – l0b0
    Oct 21, 2020 at 21:12

1 Answer 1

8

No

You cannot and should not rely on an SQL database returning rows in any deterministic fashion without proper ORDER BY clauses. In practice, rows may come back in a particular order each time you run the same query. Simply because its looking at the same data in the same order on disk. But there's no guarantee of it. Various things going on on the database server can change that physical order.

What you need to do is make sure you have the proper indexes set up. Use EXPLAIN and other tools to minimize row scans.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.