The limits of limit

[Anon]

I have a search tool on my web page that returns database rows based on search info that is provided by the user. My database is becomming quite large so this search can take quite a while. I want to limit that output to a fixed number of rows. I understand using limit in the query still selects all matching rows and then returns the number specified in the limit statement. In short it does not speed things up.

Can I specify a fixed number of rows to return and force the selection process to stop after the specified number has been selected?

Thanks

[Anon]

This depends mostly on the characteristics of your query, and how good your dbms is at minimizing the work to be done. Let's consider the best case, a query of the type:

  select * from account order by userid limit 10;

where there is a unique index on the column 'userid'. Here, a query optimizer could notice that the ORDER BY specification matches a pre-existing index, and that the LIMIT implies a small fraction of the rows will be return, and thus simply use the index to locate and return 10 rows with no further effort.