>>>>> "lindberg" == lindberg <lindberg@stripped> writes:
lindberg> I would take the opposite stand: LIMIT is extremely hard to optimize,
lindberg> since it requires the database engine to do things very differently
lindberg> from normal queries. Usually, you want _all_ the answers, and usually
lindberg> LIMIT is implemented as a cutoff on the result dataset.
MySQL already does a lot of LIMIT optimization:
From the *updated* MySQL manual:
How MySQL optimizes `LIMIT'
In some cases *MySQL* will handle the query differently when you are
using `LIMIT #' and not using `HAVING':
* If you are selecting only a few rows with `LIMIT', *MySQL* will
use indexes in some cases when it normally would prefer to do a
full table scan.
* If you use `LIMIT #' with `ORDER BY', *MySQL* will end the sorting
as soon as it has found the first `#' lines instead of sorting the
* When combinating `LIMIT #' with `DISTINCT', *MySQL* will stop as
soon as it finds `#' unique rows.
* In some cases a `GROUP BY' can be resolved by reading the key in
order (or do a sort on the key) and then calculate summaries until
the key value changes. In this case `LIMIT #' will not calculate
any unnecessary `GROUP''s.
* As soon as `MySQL' has sent the first `#' rows to the client, it
will abort the query.
* `LIMIT 0' will always quickly return an empty set. This is useful
to check the query and to get the column types of the result
* The size of temporary tables uses the `LIMIT #' to calculate how
much space is needed to resolve the query.
lindberg> Thus, I would always assume that the first solution (do it yourself)
lindberg> will be faster than the second, if the intermediate dataset is large
lindberg> and if the number of rows requested with LIMIT is a small subset of
lindberg> the total result set. As Bob says, it's easy to try, and I'
lindberg> d be happy to be surprised in any given case.
See my previous posts...
lindberg> IT of course becomes a different issue when LIMIT is combined with
lindberg> ORDER BY, when you have to look at the entire result set anyway.
lindberg> IMHO, it doesn't make sense to optimize for LIMIT in cases where you just
lindberg> want "any n results" as opposed to e.g. "the top n results". This
lindberg> use is rare and easily coded in the application.