<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
