Try using UNION instead of OR. Several posts have mentioned that OR can
dissable the use of an index. You're also right about table order being
important, there's information on the wiki about this. I would guess
that the best order is table3, table5, table4, table2, table1 since
you're actually searching on tables 3&5 and everything else follows from
them.
David Fowler wrote:
I don't think the LIKE is the problem, I used = there too. All the id
columns a primary keys, so I assume that means they're indexed well
enough.
Update on 5 table query:
I can now do the query with INNER JOINs, and it returns instantly with
the correct results. The problem appears to be the order of the tables
in the JOINs. The first table is large (4000 odd rows) and it has
absolutely no conditions on it (the second doesn't help either). What
I should have done was have the tables that result in not many rows
first, then add others as I go.
==========================================
Thanks for the very informative reply! I think you must be right about the
OR problem, infact I'm sure, because my (original) query succeeds very
quickly without it, even though the original merges SIX tables together,
using only the (,) operator (no INNER JOIN ON - Creating 1.5 billion
possible rows). But as soon as I add an OR, the query crawls to a stunning
10 minutes!
INNER JOIN must play a part in speed though too, as when using it I get down
from 10m to 15s, but that was still unnacceptable (I think the speed
increase was because the possible rows was reduced to 17 thousand-ish).
Rearranging the tables got me down to about 1s, which is just acceptable. I
think I may switch to using UNION again, because at first I didn't see it as
a real solution and carried on looking for something better. Then see what
happens, hopefully I wont even be able to time it without doing debug in my
program then, which will be nice.
Thanks again.
- Dave
