Optimize Query with JOINS and Subqueries
<p>I want to speed up one of my slower queries.</p>

<p>The problem is that I can't access the outer colum value within a subquery.</p>

<p>What I have:</p>

<pre><code>SELECT r.id AS room_id, r.room_name, coalesce(d.score,0) AS total_messages, d.latest 
FROM cf_rooms_time_frames tf
INNER JOIN cf_rooms r on r.id = tf.room_id 
INNER JOIN(
    SELECT cf.room_id, count(*) as score, max(cf.id) as latest 
    FROM cf_rooms_messages cf 
    WHERE EXISTS( 
        SELECT NULL FROM cf_rooms_time_frames tf 
        WHERE tf.start &lt;= cf.id AND ( tf.end IS NULL OR tf.end &gt;= cf.id ) 
        AND tf.room_id = cf.room_id AND tf.uid = 8
        )
    GROUP BY cf.room_id  
    ORDER BY latest 
    DESC ) d on d.room_id = r.id 

WHERE tf.uid = 8
ORDER BY coalesce(latest, score) DESC LIMIT 0, 20
</code></pre>

<p>What I want:</p>

<pre><code>SELECT r.id AS room_id, r.room_name, coalesce(d.score,0) AS total_messages, d.latest 
FROM cf_rooms_time_frames tf
INNER JOIN cf_rooms r on r.id = tf.room_id 
INNER JOIN(
    SELECT cf.room_id, count(*) as score, max(cf.id) as latest 
    FROM cf_rooms_messages cf 
    /* line added here */
    WHERE cf.room_id = tf.room_id
    /* */
    AND EXISTS( 
        SELECT NULL FROM cf_rooms_time_frames tf 
        WHERE tf.start &lt;= cf.id AND ( tf.end IS NULL OR tf.end &gt;= cf.id ) 
        AND tf.room_id = cf.room_id AND tf.uid = 8
        )
    GROUP BY cf.room_id  
    ORDER BY latest 
    DESC ) d on d.room_id = r.id 

WHERE tf.uid = 8
ORDER BY coalesce(latest, score) DESC LIMIT 0, 20
</code></pre>

<p>I think the markup explains what the query does.</p>

<p>It searches for "chatrooms" for a given user and orders them by the last message, gets the number of total message which ids are in a given range ( timeframes ), and the last message id.</p>

<p>I don't know why, but the first query returns all rows within the chatmessage table ( cf ) if I can trust EXPLAIN. It delivers the correct results but is kind of slow on a huge table.</p>

<p>I tested the second one with a "hardcoded" room_id and this one was very fast and doesn't "touched" the whole table.</p>