When is LIMIT applied? Will it select all results before limiting?

Question

I'm concerned about the performance of a query such as SELECT * FROM user LIMIT 5 on a very large user table. Will it select all records then limit to 5?

More specifically will the following query select all assetids before limiting...

SELECT * FROM assets WHERE asset_id IN(1,2,3,4,5,6,7,8,9,10) LIMIT 5

I realize it doesn't make sense to include all ids in the IN() clause if I'm limiting but I'd like to know how mysql behaves in this situation.

Thanks.

Answer 1

This depends on your query. See this page for more explanations of how LIMIT is applied: http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

For that specific query, the following would apply: "As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS."

Hope that helps.