Skip navigation links

MySQL Forums :: Newbie :: Fast 3 Table Join


Advanced Search

Re: Fast 3 Table Join
Posted by: Anthony DeFusco ()
Date: March 23, 2012 06:04AM

Hi Rick,

Thanks for the response.


>That's a "many to many" relationship between the other two table.

The relationship is not "many to many" each row in A only shows up once (if at all) in B and similarly for C. If a row from A and C are matched, then it is a unique match

>Why are there fewer 'relations' than there are rows in A and C? Are most of A and C not linked to each other?

This is just how the data is set up. Some observations from A are never matched with observations from C.

>This will give you 25 million rows; that takes a long time:
SELECT *
FROM A,B,C
WHERE A.idA = B.idA AND C.idC = B.idC
Do you ever really need to fetch all of them?

Not usually, no. Typically the query I posted also has a WHERE condition on table A or table C that limits the number of records I need to fetch. The columns that this WHERE condition references are also indexed in both A and C. For example, the data set is nationally representative but often times it is enough to pull from just one state so I can specify "WHERE A.state = "state"".

Options: ReplyQuote


Subject Written By Posted
Fast 3 Table Join Anthony DeFusco 03/21/2012 07:57AM
Re: Fast 3 Table Join Peter Brawley 03/21/2012 08:07AM
Re: Fast 3 Table Join Anthony DeFusco 03/21/2012 08:08AM
Re: Fast 3 Table Join Peter Brawley 03/21/2012 08:16AM
Re: Fast 3 Table Join Rick James 03/22/2012 10:01PM
Re: Fast 3 Table Join Anthony DeFusco 03/23/2012 06:04AM
Re: Fast 3 Table Join Rick James 03/24/2012 09:49PM
Re: Fast 3 Table Join Anthony DeFusco 03/24/2012 10:04PM
Re: Fast 3 Table Join irek kordirko 03/25/2012 05:59AM
Re: Fast 3 Table Join Rick James 03/26/2012 10:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.