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

bkline> In my previous message (before running EXPLAIN) I wrote:
>> I should think it's pretty clear that MySQL is not using the more 
>> sophisticated optimizations needed to support your answer that the 
>> second approach would be faster, but is instead using the cruder 
>> approach described about as optimization #1.

bkline> Here's the nail in the coffin, direct from the source:

mysql> EXPLAIN
-> SELECT
-> product.code,
-> product.name,
-> SUM(priprod.value)
-> FROM
-> product,
-> priprod
-> WHERE
-> product.code=priprod.code
-> GROUP BY
-> product.code
-> LIMIT 0, 25;
bkline> +---------+--------+---------------+-----------+---------+---------+
bkline> | table   | type   | possible_keys | key       | key_len | rows    |
bkline> +---------+--------+---------------+-----------+---------+---------+
bkline> | priprod | ALL    | priprod_code  | NULL      |    NULL | 1000000 |
bkline> | product | eq_ref | prod_code     | prod_code |       8 |       1 |
bkline> +---------+--------+---------------+-----------+---------+---------+
bkline> 2 rows in set (0.00 sec)

bkline> I've removed the 'extra' column, which is empty, and the 'ref' column,
bkline> which has priprod.code for the second row, in order to allow the
bkline> output to fit on an 80-column fixed-width display.

Hi!

Could you please try:

SELECT STRAIGHT_JOIN product.code, product.name, SUM(priprod.value)
FROM product, priprod WHERE product.code=priprod.code GROUP BY product.code
LIMIT 0, 25;

As I said; The problem is not that MySQL can't do the optimization ;
The problem is to force MySQL 3.22 to use the right method :)

Whenever you have time;  Please check this up in MySQL 3.23...
(MySQL 3.23 should do it better, but I think we still have to add a
couple of extra checks for the case of GROUP BY + LIMIT to make this
optimal)

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