[Solved] Joining Multiple Tables Based on Two Cross Reference Tables

EverSQL Database Performance Knowledge Base

Joining Multiple Tables Based on Two Cross Reference Tables

Database type:

I have a database (MySQL 5.1) that uses a cross reference table (local_ref in the example below) to get an numerical ID for a value. I've introduced another cross reference table (foreign_ref, below) to reference these numerical IDs to an index in another database. Normally that's not a complicated join, however, I have multiple columns that use the keys from the cross referenced table (val1 and val2, below).

E.g.:

mysql> select * from foo;
+-----+------+------+
| id  | val1 | val2 |
+-----+------+------+
| 100 | A    | B    |
| 200 | A    | D    |
| 300 | B    | C    |
+-----+------+------+

mysql> select * from local_ref;
+----+-------+
| id | value |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |
|  4 | D     |
|  5 | E     |
+----+-------+

mysql> select * from foreign_ref;
+----------+------------+
| local_id | foreign_id |
+----------+------------+
|        1 |         10 |
|        2 |         20 |
|        3 |         30 |
|        4 |         40 |
+----------+------------+

What I need is the following:

+-----+---------+---------+
| id  | val1_id | val2_id |
+-----+---------+---------+
| 100 | 10      | 20      |
| 200 | 10      | 40      |
| 300 | 20      | 30      |
+-----+---------+---------+

Knowing that the original table isn't normalized as it should be, I've achieved the results the following two ways:

Aliasing both cross-reference tables twice:

SELECT
FOO.id, F_R1.foreign_id, F_R2.foreign_id
FROM FOO 
JOIN
Local_Ref as L_R1 ON (FOO.val1 = L_R1.value)
JOIN
Local_Ref as L_R2 ON (FOO.val2 = L_R2.value)
JOIN
Foreign_Ref as F_R1 ON (L_R1.id = F_R1.local_id)
JOIN
Foreign_Ref as F_R2 ON (L_R2.id = F_R2.local_id)

Joining the cross-reference tables twice and aliasing each join.

SELECT
FOO.id, joint1.foreign_id, joint2.foreign_id
FROM
FOO
JOIN
(
SELECT * FROM Local_Ref JOIN Foreign_Ref ON Local_Ref.id = Foreign_Ref.local_id
) as joint1
ON FOO.val1 = joint1.value
JOIN
(
SELECT * FROM Local_Ref JOIN Foreign_Ref ON Local_Ref.id = Foreign_Ref.local_id
) as joint2
ON FOO.val2 = joint2.value

I feel like both approaches are pretty inefficient and could be improved. Aside from reconstructing the database, are there any more efficient solutions?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `Foreign_Ref` ADD INDEX `foreign_ref_idx_local_id` (`local_id`);
ALTER TABLE `Local_Ref` ADD INDEX `local_ref_idx_value` (`value`);
The optimized query:
SELECT
        FOO.id,
        F_R1.foreign_id,
        F_R2.foreign_id 
    FROM
        FOO 
    JOIN
        Local_Ref AS L_R1 
            ON (
                FOO.val1 = L_R1.value
            ) 
    JOIN
        Local_Ref AS L_R2 
            ON (
                FOO.val2 = L_R2.value
            ) 
    JOIN
        Foreign_Ref AS F_R1 
            ON (
                L_R1.id = F_R1.local_id
            ) 
    JOIN
        Foreign_Ref AS F_R2 
            ON (
                L_R2.id = F_R2.local_id
            )

Related Articles



* original question posted on StackOverflow here.