List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 10 1999 11:55pm
Subject:RE: Query optimizing
View as plain text  
Hi!

>>>>> "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
     whole table.

   * 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
     columns.

   * 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.

Regards,
Monty
Thread
Query optimizingManik Surtani8 Oct
  • Re: Query optimizingsinisa8 Oct
    • Re: Query optimizingBob Kline8 Oct
      • Re: Query optimizingsinisa8 Oct
        • Re: Query optimizingBob Kline8 Oct
          • Re: Query optimizingBob Kline8 Oct
            • RE: Query optimizingbkline9 Oct
              • RE: Query optimizingsinisa9 Oct
                • RE: Query optimizingBob Kline9 Oct
                  • Re: Query optimizingTonu Samuel10 Oct
                    • Re: Query optimizingBob Kline10 Oct
                  • RE: Query optimizingLeif Neland10 Oct
                  • RE: Query optimizingsinisa10 Oct
                    • RE: Query optimizingBob Kline10 Oct
                      • RE: Query optimizingBob Kline10 Oct
                        • RE: Query optimizingbkline10 Oct
                          • RE: Query optimizingsinisa11 Oct
                            • RE: Query optimizingBob Kline11 Oct
                        • RE: Query optimizingPatrick Greenwell10 Oct
                      • RE: Query optimizingsinisa11 Oct
                        • RE: Query optimizingBob Kline11 Oct
              • RE: Query optimizingMichael Widenius11 Oct
                • RE: Query optimizingBob Kline11 Oct
                • RE: Query optimizingBob Kline11 Oct
                  • RE: Query optimizingBob Kline11 Oct
            • Re: Query optimizingMichael Widenius11 Oct
      • Re: Query optimizingMichael Widenius11 Oct
RE: Query optimizinglindberg10 Oct
  • RE: Query optimizingBob Kline10 Oct
    • RE: Query optimizingMichael Widenius11 Oct
  • RE: Query optimizingMichael Widenius11 Oct
Re: Query optimizingRenato Lins10 Oct
  • Re: Query optimizingTonu Samuel10 Oct