CODEWITHSUNDEEP
mysqlsqlselectlimitwhere-clause
Filippo oretti
Filippo oretti

Reputation: 49817

SQL how limit and offset are executed?

i would like to know if limit and offset are executed after the row were selected:

SELECT * FROM users WHERE id > 4 LIMIT 0,90 ORDER BY datetime DESC;

Does this query first selects all the users's rows then apply the LIMIT, or does this query first apply the LIMIT then selects the users's rows?

Upvotes: 3

Views: 3530

Answers (4)

Gavin Towey
Gavin Towey

Reputation: 3200

So the question you ask doesn't quite make sense here.

What you should really be asking is, "Does MySQL have to find all rows that match the WHERE clause in the query, or can it apply the LIMIT and only have to read at most N number of rows?"

There are two possible cases here, and one extra thing to consider, and a problem with your original query.

  1. If you're using LIMIT you should be using ORDER BY. MySQL has no "natural" order, which means the rows you actually get back from that query is not deterministic. This is a bad thing.

So now that you're using ORDER BY there are two possibilities.

  1. MySQL can use an index to satisfy the ORDER BY clause. In this case, it can scan the table in index order looking for matches until the LIMIT is satisfied and then it will stop reading there. This is efficient.

  2. MySQL can't use an index. Then it will have to find all possible matches and sort the entire result set before applying the LIMIT. This is generally pretty slow if there are many rows.

The additional thing to consider is that when you use an offset with limit, mysql has to read all rows up to the starting position before it will return results. This becomes very slow when the offset value is very large.

This slide deck on efficient pagination in MySQL explains a lot: http://www.scribd.com/doc/14683263/Efficient-Pagination-Using-MySQL

Upvotes: 2

Vikram Jain
Vikram Jain

Reputation: 5588

Select Query

SELECT * FROM users WHERE id > 4 LIMIT 0,90;

Upvotes: 1

andy
andy

Reputation: 6079

From http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.

Main Reference Link

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

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.

Upvotes: 4

Related Questions