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