Is MySQL Limit Applied Before or After Order By

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.

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

does LIMIT have effect before or after JOIN?

Don't worry. The LIMIT will happen at the same time with the join: MySQL will not read through the entire logins table, but fetch line by line (joining each time on users) until it has found 10 lines.

Do note that if a users.id appears two times in the table, the JOIN will duplicate the logins line and add each users line. The total amount of lines will still be 10, but you'll have 9 logins.

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.

Why a LIMIT clause is placed after ORDER clause in MySQL?

Your SELECT statement has two (2) clauses: ORDER BY and LIMIT.

Per the MySQL SELECT syntax, clauses are defined in a specific sequence as shown in the following image from the MySQL website's SELECT syntax page.

The ORDER BY clause is allowed before the LIMIT clause, but not the reverse.

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.

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.

SQL how limit and offset are executed?

The FROM clause is the first to be executed in the query, then the WHERE clause. After that the LIMIT clause is applied.

So, the following are the Logical query processing steps for the query you posted:

• FROM clause return all users.
• WHERE clause is applied. Only the users with id > 4 get passed to the next step.
• Then LIMIT.

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.

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.

---

Related Topics