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

[Daniel Brennan]

Hi,

I have solved my development problem, but I am puzzled by the results. I thought I would share the puzzle with all you evil genius's out there. By the way, before I give you the problem, I do know that a left outer join was unnecessary in this example. I am used to using them because of other requirements I am working with. The queries listed were not the actual ones, but very similar. They are the queries I used to troubleshoot the problem I was having. Here goes:

Suse 9.1 Pro, (Ill be switching to 9.0 Enterprise 64 bit soon)
dual processor 64 bit pentium 4 3.5 ghz
Mysql 4.0.18 non transactional database

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)

The b.tranid is a primary, all other fields are indexed except the b.amount field.
Notice that the 2nd query, which reproduces the development problem I had, takes significantly longer that the first query using only a regular join. Why is this?

The other developer here and I are both programmers, not DBA's. So we don't know but here are our guesses:

1st: No optimizations being used due to sum column on second table.
2nd: Query looking through every record of right table.
3rd: MySql bug.

Anyway, Im looking forward to your guesses or correct answers :)

Dan