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.
signature.asc
Description: OpenPGP digital signature
