Converting a doubly-nested query to a JOIN statement, and other optimizations

Question

I've got this query which is quite specific and returns exactly what I need. As we know, nested queries in mysql can be pretty terrible in terms of runtime. If anyone could help me convert the following code to a JOIN statement, I would really appreciate it.

Any other optimizations are welcome and encouraged, of course.

SELECT title 
FROM   exp_channel_titles 
WHERE  entry_id IN (SELECT rel_child_id 
                    FROM   exp_relationships 
                    WHERE  rel_id IN (SELECT field_id_202 
                                      FROM   exp_channel_data 
                                      WHERE  entry_id = 19971));

Thank you!

Answer 1

SELECT DISTINCT
  titles.title
FROM
  exp_channel_titles  AS titles
INNER JOIN
  exp_relationships   AS rel
    ON titles.entry_id = rel.rel_child_id
INNER JOIN
  exp_channel_data    AS channel
    ON rel.rel_id = channel.field_id_202
WHERE
  channel.entry_id = 19971

The DISTINCT is there incase you have many:many relationships, etc, that may otherwise create duplicates.

In terms of optimisations, ensure each table has indexes on the fields that you use in JOINs and WHERE clauses.

Answer 2

Another alternative:

SELECT t.title 
  FROM exp_channel_titles t
  JOIN ( SELECT r.rel_child_id 
           FROM exp_relationships r
           JOIN exp_channel_data d
             ON d.field_id_202 = r.rel_id
          WHERE d.entry_id = 19971
          GROUP BY r.rel_child_id 
       ) c
    ON c.rel_child_id = t.entry_id

This query still includes an inline view (or "derived table", in MySQL lingo), but this query will return a resultset equivalent to the original query, without introducing or eliminating any "duplicate" titles from/in the exp_channel_titles table.

Note that other solutions may introduce duplicates, due to the nature of the way the JOIN handles one-to-many relationships. You can avoid returning any duplicates that are introduced by using a DISTINCT keyword or a GROUP BY clause, but this will also eliminate any duplicates that were being returned in the original query.