List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 10 1999 11:57pm
Subject:RE: Query optimizing
View as plain text  
>>>>> "Bob" == Bob Kline <bkline@stripped> writes:

Bob> On 10 Oct 1999 lindberg@stripped wrote:
>> I would take the opposite stand: LIMIT is extremely hard to optimize,
>> since it requires the database engine to do things very differently
>> from normal queries. Usually, you want _all_ the answers, and usually
>> LIMIT is implemented as a cutoff on the result dataset.
>> 
>> Thus, I would always assume that the first solution (do it yourself)
>> will be faster than the second, if the intermediate dataset is large
>> and if the number of rows requested with LIMIT is a small subset of
>> the total result set. As Bob says, it's easy to try, and I'
>> d be happy to be surprised in any given case.
>> 
>> IT of course becomes a different issue when LIMIT is combined with
>> ORDER BY, when you have to look at the entire result set anyway.
>> 
>> IMHO, it doesn't make sense to optimize for LIMIT in cases where you just
>> want "any n results" as opposed to e.g. "the top n results". This
>> use is rare and easily coded in the application.
>> 

Bob> On the other hand, perhaps if we let Sinisa persist long enough with the
Bob> line of argument that only a dolt would believe MySQL unable to optimize
Bob> this properly, then maybe the embarrassment will compel him to go ahead
Bob> and implement the optimization once he realizes it isn't done yet
Bob> (assuming my analysis is correct).  (Just kidding, Sinisa. :->} )

Bob> I agree that LIMIT is hard to optimize (that's why I was doubtful that
Bob> MySQL would support Sinisa's answer), but I don't think I'd go as far as
Bob> "extremely" hard, and at least one DBMS (SQL Server) nails it properly
Bob> (that is, Sinisa's answer would be correct for that product).

Bob> Even the LIMIT combined with ORDER BY is do-able without looking at a
Bob> complete intermediate joined result set, as long as the ORDER BY is on
Bob> the same column used for the join (as would be likely in this case).

MySQL already handles this case very efficiently if the ORDER BY is an
index in the first table in EXPLAIN!

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