query optimization for many to many join

Hi Everybody,

Does anyone have an suggestions on how to optimize a join on data from both sides of a many to many relationship? It sees that I can get very quick queries for anything on either side of the many to many but when a try to join accross the many to many relationship there is no way to do it quickly.

For example, if I have an address table and a person table and a person can have multiple addresses and an address can be occupied by multiple people so I join person and address in a table person_address then...

If I try to get all of the person data I can join to the person_address table using an index on person_id and address_id. But then if I try to join in address information I am using the person_id index which is useless for the join on address.

The same holds trure swapping person and address in the above paragraph.

Any suggestions would be greatly appreciated.

Thanks,
John Received on Thu May 31 2007 - 19:05:06 CDT