List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 10 1999 11:29pm
Subject:Re: Query optimizing
View as plain text  
<cut>

Bob> Here's as far as I've gotten with the second approach, in which
Bob> "everything is done on the server":

mysql> SELECT
-> product.code,
-> product.name,
-> SUM(priprod.value)
-> FROM
-> product,
-> priprod
-> WHERE
-> product.code=priprod.code
-> GROUP BY
-> product.code
-> LIMIT 0, 25;
Bob> ERROR 1114: The table 'SQL374_0' is full

Bob> I'll move TMPDIR to a larger filesystem (right now there's about 1.7GB
Bob> available) if you're still skeptical, but it takes longer just to get to
Bob> this error message than it does to get the complete job done using the
Bob> first approach over the slow modem line, so I should think it's pretty
Bob> clear that MySQL is not using the more sophisticated optimizations
Bob> needed to support your answer that the second approach would be faster,
Bob> but is instead using the cruder approach described about as optimization
Bob> #1.  I'm running 3.22.25 on Linux 2.2.5-15, so it may well be that you
Bob> can come back and tell me that a more recent development version has
Bob> souped up the optimizer here, but for this version at least, this is a
Bob> case where it pays to let the client take advantage of some extra
Bob> smarts.

Bob> So the morals of the story are:
Bob> 1. Good rules of thumb don't always apply.
Bob> 2. When in doubt, try it out.

What do EXPLAIN say about the above query?  is 'product' the first or
second table?   If not, then MySQL can't use the ORDER BY
optimization.

In the above case, MYSQL tries to do a temporary summary table. on
code. (This is the first approach I mentioned in my previous query).

When things concerns GROUP BY MySQL 3.23 is MUCH better than 3.22, so
things may be very different in this case.  (But as always, there is
still much one can do to make this even better!)

In MySQL 3.22 you can try forcing PRODUCT to be the first table with
STRAIGHT_JOIN.  This may force MySQL to use the second (in this case
fast) method.

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