Level 75
Normally the limit is last. I would suggest looking up the limit clause in the MySql manual.
Also here:
Jan 14, 2025
7
Level 7
Does the order of mysql queries matter?
e.g,
select * from `users` order by `created_at` desc limit 10
select * from `users` limit 10 order by `created_at` desc
does the different order of execution affect performance?
Level 7
@jlrdw I mean does the performance is different when ordering 10 items vs ordering all then taking 10?
Level 75
@amir5 I am pretty sure in mysql it doesn't matter. I believe mysql will order first either way. Again I suggest a read here:
https://dev.mysql.com/doc/refman/8.4/en/limit-optimization.html
1 like
Level 73
@amir5 Limit should always be last in your query together with offset. Like @jussimannisto stated the second is not valid SQL.
However in general the order of you write the SQL doesn't really matter, and in the few cases it does it when you need to fool the optimizer, but the optimizer is most likely much smarter than you are.
When it comes to performance of your queries, indexes is way more important for your performance than any trickery with trying to fool the optimizer to create a different execution plan.
You should use EXPLAIN to analyze what in your queries go slow, and if it does make the needed changes to remove the slowness.
1 like
Level 50
The first query is valid SQL, the second is not.
1 like
Level 122
My sql will always optimize the query before execution, but first it has to be valid syntax
1 like
Level 5
Yes, the first is slower and returns data. The second is much faster, but will return nothing because it’s not valid SQL 😉
1 like
Please or to participate in this conversation.