Re: Puzzled: why does join run 27,252 x faster than left outer join?

[Felix Geerinckx]

Daniel Brennan wrote:

> The Strange results:
>
> mysql> select sum(b.amount)*-1
> -> from lockbox a join ledger b
> -> on a.payid = b.tranid
> -> or a.secid = b.tranid
> -> or a.petid = b.tranid;
> +------------------+
> | sum(b.amount)*-1 |
> +------------------+
> | 134429.57 |
> +------------------+
> 1 row in set (0.02 sec)
>
> mysql> select sum(b.amount)*-1
> -> from lockbox a left outer join ledger b
> -> on a.payid = b.tranid
> -> or a.secid = b.tranid
> -> or a.petid = b.tranid;
> +------------------+
> | sum(b.amount)*-1 |
> +------------------+
> | 134429.57 |
> +------------------+
> 1 row in set (9 min 44.46 sec)

A LEFT JOIN forces a table read order
(see http://dev.mysql.com/doc/mysql/en/left-join-optimization.html)

Would you care to post the result of

EXPLAIN SELECT ... \G

for both queries?

--
felix
Please use BBCode to format your messages in this forum.