How can I speed up an sql query that round trips between two tables I first posted this question on stackoverflow, and now I see this site, seems more relevant. 

These tables are not that big, but the time taken to process this query is long.

What can I do with the following queries to speed up the process taken to return results?

$chat_alg = mysqli_query($con, "
    SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time &gt; NOW() - INTERVAL 3 DAY &amp;&amp; sid != '' 
    GROUP BY sid
    ORDER BY COUNT(*) DESC
") or die(mysqli_error($con));

while($row_chat_alg = mysqli_fetch_array($chat_alg)) {
    $chart_songs[] = $row_chat_alg['sid'];
}

$imploded_songs = implode($chart_songs, ',');

$projects = $dbh-&gt;query("
       SELECT * FROM music
       WHERE perms != 'c' &amp;&amp; sid IN($imploded_songs) 
       ORDER BY FIELD(sid,$imploded_songs) 
       limit 50
");

...


This tables are growing, I'm worried that maintaining them will be a problem if this is already slow.
From my (rather limited) knowledge of php, I assume you want to combine the two queries into one:

SELECT m.*, p.frequency 
FROM 
  ( SELECT sid, COUNT(*) as frequency
    FROM plays
    WHERE time &gt; NOW() - INTERVAL 3 DAY 
      AND sid &lt;&gt; '' 
    GROUP BY sid
    ORDER BY COUNT(*) DESC
    LIMIT 50 
  ) AS p
  JOIN music AS m
    ON m.sid = p.sid
WHERE m.perms &lt;&gt; 'c' 
ORDER BY m.frequency DESC ;


As for efficiency, it's better to tune the first query separately and then this combined one. 

An index on plays (sid, time) should help for the first query. For the second, if there is an index on music (sid), it should be quite efficient as it's a join to only 50 rows of music.