Three(or Four)-Way Joins

[Malcolm Wardlaw]

I have some data that's separated out across multiple tables that I need to join into a single query. The tables are all 1-to-1 unique to each other with a single identifier, and the tables contain an identical number of observations, i.e. no table contains an identification key that another table does not. The reason they are spread across multiple tables is because of size limitations. There are over 3000 variables unique to each key, and normalizing it further does not resolve the problem. Size constraints require separating the columns into groups.

Here's my question, I want to create a query whereby I join a table containing a small number of sub identifiers "X" to a select group of variables across several tables that are linked by the same 1-to-1 key "K". I should mention that there are over a million rows in each of these tables.

So:

Table 1("X") <--> Table 2("K, X, A") <--> Table 3("K, B") <--> Table 4("K, C")


With Table 1 being only around 500 rows and Table 2,3,4 being over a million.


My question is on how to form the query in the most efficient way possible. I'm guessing that the order in which MySQL chooses to join the tables and select from them is critically important.

One more thing, this type of query is going to be issued quite a bit using "X", so if there is any benefit to permanently inserting "X" into Tables 3&4 (in this example) I can do that. Also, "X" is indexed in Table 2 (and again, could be in 3&4).