MySQL Forums :: Newbie :: Fast 3 Table Join

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"".