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

Bob> On Fri, 8 Oct 1999 sinisa@stripped wrote:
>> Manik Surtani writes:
>> > Hi,
>> > 
>> > Can anyone tell me which of the 2 following ways
>> > to extract data are faster/more efficient?
>> > 
>> > 1.  Method 1
>> > **********
>> > 
>> > SELECT NAME, CODE FROM PRODUCT LIMIT 0, 25;
>> > 
>> > ... Start Loop: for each row obtained above,
>> > 
>> >     SELECT SUM(VALUE) FROM PRIPROD WHERE
>> > CODE='code_obtained_above';
>> > 
>> > ... End Loop
>> > 
>> > 
>> > 2.  Method 2
>> > **********
>> > 
>> > SELECT
>> >     PRODUCT.CODE,
>> >     PRODUCT.NAME,
>> >     SUM(PRIPROD.VALUE)
>> > FROM
>> >     PRODUCT,
>> >     PRIPROD
>> > WHERE
>> >     PRODUCT.CODE=PRIPROD.CODE
>> > GROUP BY
>> >     PRODUCT.CODE
>> > LIMIT 0, 25;
>> > 
>> > 
>> > 
>> > The reason I ask is because both PRODUCT and
>> > PRIPROD tables have in excess of 1 million rows,
>> > and this query needs to be very efficient.  If
>> > there is a still better way to do this (using
>> > joins, whatever) pls let me know .....
>> > 
>> > Thanks in advance,
>> 
>> Seconde one. Definitely.
>> 
>> Sinisa
>> 

Bob> Can you elaborate?  It doesn't seem patently obvious that this would be
Bob> true, unless one can be confident that MySQL will do some extra
Bob> optimization (specifically, that it will perform the LIMIT and the GROUP
Bob> BY simultaneously, and thus avoid building the full joined candidate set
Bob> for the WHERE clause).  Full marks for MySQL if this is true, but as
Bob> this possibly isn't as much of a straightforward case as your brief
Bob> answer might imply, it might be good to get explicit confirmation of
Bob> what MySQL will do in this case (I don't think EXPLAIN tells everything
Bob> one needs to know here, though I could be wrong).

Bob> Of course, Manik, you could always find out the direct way, by trying
Bob> and timing both approaches.

Hi!

There is two ways to solve the second query:

- Doing a sort on product.  When the product change, output the
  summary information and continue with next product.

- Create a temporary table with a key on product and update/insert new 
  rows into it.  In this case one has to create the whole result set
  before one can get the first 25 rows.

MySQL supports both ways, but it's not that easy to know which is used
without testing :)

It's likely that MySQL will use the first option for the above query
(in which case Sinisa is right), but this is not guaranteed.

MySQL 3.22 tries to prioritize the first method, while MySQL 3.23
usually uses the second way (as this is normally faster).

In MySQL 3.23 you can force the usage of the first method by doing:

SELECT SQL_BIG_RESULT ....

For the moment the only way to verify this is by doing a
'mysqladmin proc' and check that the query doesn't have a status line
of 'copying to temp table'.

Before anyone comments on this:  Yes, we know that we have to extend
the output from EXPLAIN.  The problem is how to report things that is
general for the whole query.  We have planed to fix this when MySQL can
return more than one result set for one query...

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