Re: query optimization for many to many join

On Jun 1, 8:05 am, grasp06110 <grasp06..._at_yahoo.com> wrote:
> 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

I'm confused by what you're trying to do. Can you show us what kind of results you're looking for.

And oh, just to confirm, you now have 3 tables:

person (person_id, <other person data>)
address (address_id, <other address data>) person_address (person_id, address_id)

Correct?

Cheers,

Romeo Received on Thu May 31 2007 - 22:52:34 CDT