How Does MySQL Process Order by and Limit in a Query

How does MySQL process ORDER BY and LIMIT in a query?

It will order first, then get the first 20. A database will also process anything in the WHERE clause before ORDER BY.

Order by / limit execution in SQL

The optimization mentioned in the documentation generally only works if there's an index on the publish_date column. The values are stored in the index in order, so the engine simply iterates through the index of the column, fetching the associated rows, until it has fetched 20 rows.

If the column isn't indexed, the engine will generally need to fetch all the rows, sort them, and then return the first 20 of these.

It's also useful to understand how this interacts with WHERE conditions. Suppose the query is:

SELECT article
FROM table1
WHERE last_read_date > '2018-11-01'
ORDER BY publish_date
LIMIT 20

If publish_date is indexed and last_read_date is not, it will scan the publish_date index in order, test the associated last_read_date against the condition, and add article to the result set if the test succeeds. When there are 20 rows in the result set it will stop and return it.

If last_read_date is indexed and publish_date is not, it will use the last_read_date index to find the subset of all the rows that meet the condition. It will then sort these rows using the publish_date column, and return the first 20 rows from that.

If neither column is indexed it will do a full table scan to test last_read_date, sort all the rows that match the condition, and return the first 20 rows of this.

Order of execution of ORDER BY and LIMIT in a MySQL query

A SQL LIMIT will in all databases always work on the result of a query, so it will run the query with the ORDER BY and that result will then be limited. This is a functional requirement, the database not perse needs to execute it like that.

Thus the 1st way.

MySQL Default Sort Order with Limits and Offsets - Some Items Ignored - Why?

SQL tables and result sets (with no ORDER BY) represent unordered sets. Ordering is arbitrary and capricious. It can change from query execution to query execution.

This query:

SELECT SQL_CALC_FOUND_ROWS *
FROM test
WHERE test_customer = '1'
LIMIT 4 OFFSET 4;

Returns an arbitrary 4 rows for the customer. The OFFSET means nothing.

If you want data in a particular order, then use ORDER BY. Otherwise, the data is in an arbitrary order that can change from query execution to query execution. There is no "default ordering".

So:

SELECT SQL_CALC_FOUND_ROWS *
FROM test
WHERE test_customer = 1
ORDER BY testid
LIMIT 4 OFFSET 4;

Note that I removed the single quotes from the =. The value is an integer, so use a numeric constant.

Apply ORDER BY after query and LIMIT

Since you're aliasing t.id to t_id, you need to use the alias in the outer query.

SELECT *
FROM (select DISTINCT t.id t_id, t.cart_id ,tS.id tS_id, tS.created tS_created, t.value, t.transactionType_id tT_id, tS.member_name, outIn, tT.type type

    from transaction t
    join transactionSummary tS ON tS.id = t.transactionSummary_id
    left join transactionType tT ON tT.id = t.transactionType_id
    limit 50) x
ORDER BY t_id DESC

BTW, the way you wrote DISTINCT(t.id) suggests that you think the distinct operation is only being applied to that one column. DISTINCT applies to the entire SELECT list; if you only want to make certain columns distinct, you must use GROUP BY to specify those columns.

Here's a possible way to rewrite the query that may make it faster:

select DISTINCT t.id t_id, t.cart_id ,tS.id tS_id, tS.created tS_created, t.value, t.transactionType_id tT_id, tS.member_name, outIn, tT.type type
from transaction t
join (select max(id)-500 maxid from transaction) mT on t.id > maxid
join transactionSummary tS ON tS.id = t.transactionSummary_id
left join transactionType tT ON tT.id = t.transactionType_id
order by t_id DESC
limit 50

By filtering down to just the top 500 IDs, the size of the joins and sorting are reduced.

how to orderby after applying limit in sql

This query solves my question thank you all for suggestions,

SELECT * FROM (SELECT * FROM table WHERE coloumn='myFilter' ORDER BY serialnumber desc LIMIT 3) a ORDER BY serialnumber asc

the query uses to select the latest 3 rows ordered by big to small serial number then again the selected rows order where reversed, thnx @Kelvin Barsana

Mysql: Use order by and limit into separate queries when using union all

Use parentheses:

(select ... order by ... limit ...) 
union all
(select ... order by ... limit ...) 
union all
(select ... order by ... limit ...) 
union all
...

Why is the `ORDER BY RAND()` statement not working in my query?

You don't need UNION:

SELECT * 
FROM products 
ORDER BY stock = 0, RAND();

The condition stock = 0 in the ORDER BY clause makes sure that the zero-stock products are placed last and the 2nd level of sorting with RAND() randomizes the rows in each of the 2 groups.

SQL Fiddle

Is MySQL LIMIT applied before or after ORDER BY?

Yes, it's after the ORDER BY. For your query, you'd get the record with the highest publishedOn, since you're ordering DESC, making the largest value first in the result set, of which you pick out the first one.

---

Related Topics