Select additional rows depending on each row's value

user_16471087 0
Viewed 31

What I'm trying to do is: I'm trying to build a comments and replies system for my website. I already have this working, but the way I'm doing is probably not the best for performance.

I want to select 10 rows from a table that contains the comments, then I want to select 2 additional rows from another table that contains the replies for each of these comments. I do that by having a loop on PHP to select 2 replies from another table for each comment. Something more or less like this:

$comments = $MySQL->fetchRows("SELECT id, text FROM comments LIMIT 10");

foreach($comments as $i => $c) {
    $comments[$i]["replies"] = $MySQL->fetchRows("SELECT id, text FROM replies WHERE comment_id = $c['id'] LIMIT 2");
}

Like I said, I'm sure this isn't the most optimal way of doing it, since it requires multiple calls to the database. Is there a better way of doing this in a single query using MySQL?

mysql php
1 Answers

I often have 40 well-tuned queries on a single web page. It is not bad.

On the other hand, JOINs, UNIONs, Stored Procedures, etc can cut down the number of roundtrips to the server.

Notes:

  • A LIMIT without an ORDER BY does not make much sense.

  • The two queries you have can be combined using a JOIN and a "derived table".

      SELECT c.text, r.text 
      FROM ( SELECT id, text FROM comments
              WHERE ...
              ORDER BY ...
              LIMIT 10 ) AS c
      JOIN replies AS r
           ON r.id = c.id   -- Really the same id??

That will find all the replies from some 10 "comments".

To have limits on both gets trickier.

user_17668310
Related