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?
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.LIMIT. 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.
So now that you're using ORDER BY there are two possibilities.
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.
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
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.
Select Query
SELECT * FROM users WHERE id > 4 LIMIT 0,90; 
