>>>>> "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
