Hi,
> Ok, I'm out of ideas. The additional index should have sped things up,
not
> slowed them down. I've got some tables with 26,000 rows in it and I do 4
> joins (using a WHERE clause) with smaller tables without a hitch.
>
> Sorry I can't help.
But thanks a lot for trying. :) And thanks also to everyone else who
replied.
It appears the general consensus is that MySQL just can't use an index with
that type of LEFT JOIN. Exactly why is still a mystery to me.
If I "simulate" a LEFT JOIN by using multiple queries it's faster than using
a single query with an actual LEFT JOIN. And interestingly, Steve Ruby
reported to me that when he tried a LEFT JOIN query similar to mine on his
large database it took 12 minutes(!) to execute, whereas when he used
multiple queries and no LEFT JOIN it took 0.00 seconds each time.
I know very little about MySQL's internals, but it *seems* like MySQL is
doing the join at the wrong time. Instead of narrowing down to LIMIT x rows
and then doing the join, it is joining all rows and then narrowing down to
LIMIT x rows. And that explains why it refuses to use an index.
That's all my speculation though. (If only a MySQL developer could clarify
this... ;)
Jordan Russell
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
