Is it okay to SELECT ALL ROWS from MYSQL, then process it with PHP, and return a limited number of rows as JSON?

Question

I'm creating a website which has posts and a commenting section. I'm very interested in "Lower bound of Wilson score confidence interval for a Bernoulli parameter" sorting algorithm which is explained by Evan Miller here.

And, my comment system has nested comments. How I store the comments in the postscomments table in MYSQL is as follows.

Id | ParentId | Comment             | PostId | ...
1    NULL       Parent Comment        1
2    1          Child comment         1
3    2          Grand Child comment   1

Inserting, updating, and deleting just work as a piece of cake.

Selecting is the worst thing. Let me tell you how I need it to be selected.

When I receive an AJAX request for my get-comments.php file, I need to get the comments for a specific post. (AJAX request sends the post ID). So, I need to select the comments of that post sorted according to that algorithm.

But, the problem is that I only need to select 30 parent comments, and 5 child comments per each parent. I'm not sure about a way to do the sorting and grouping at once in MYSQL query. Here's what I tried.

• Recursive Query It didn't work for two reasons. I cannot group the results into parents and I cannot limit the resutls. (LIMIT is not supported inside WITH RECURSIVE)
• Other hierarchical storage methods I tried some of them. But, they didn't work too.

So, finally I came up with SELECTING ALL and processing with PHP which succeeded 100%.

Here's the long PHP script that does the thing. It works very well. Returns grouped results, sorted correctly.

But, the problem I have is that, I have no comments limit per each post. So, If I get 100,000 or more comments per post, will my server get overloaded? There's not just 1 post, so I think this is not the best solution.

Do you have a better solution this?

Is there a way that I can do all of these things in a MYSQL query and reduce the number of rows that I select?

Thanks in advance.

Added:

Here's the structure of JSON Response that I need to get.

{
   "parent": [
        // sorted parent comments
        {
            "Id": 4,
            "UserId": 2,
            "PostId": 1,
            // and other comment data
        },
        {
            // another comment
        }
   ],
   "4": [
       // sorted array of children of comment 4
       {
           // comment 4's  child
       },
       {
           // comment 4's child
       }
   ],
   "6": [
       // sorted array of children of comment 6
   ]
}

Is there a way that I can first sort the comments on that algorithm, then select only the first 30 parent comments and only 5 child comments per each parent just using a MYSQL Query? (If I could do that, I can group that result to this JSON with PHP)

Answer 1

Everytime you are storing new vote, do:

1. Store sum of upvotes and downvotes on each comment (you can do just "current+=1") - you will get rid of the "LEFT JOIN postscommentsvotes"
2. Store the result of the crazy expression in ORDER BY into a column in the comments table as well. (lets call it Priority)

Separate the search query into two both using the precomputed Priority for sorting:

1. searches for parents with WHERE PostId = ? ORDER BY Priority DESC LIMIT 30. Compount key (PostId, Priority) may be useful.
2. query for all Id, ParentId WHERE PostId = ? ORDER BY ParentId, Id
3. construct a tree from the result and exclude branches not belonging under comments from first query.
4. query for children using ParentId IN (?) with parent ids being all ids of comments from given branch of the preconstructed id-tree (do this for each parent or compose a UNION)

Alternatively, query step 2 and process step 3 for each parent separately, but they may overlap, hard to say what will be better.

Btw, the first part may be optional, but since you split the search query in two both using the Priority, and potentialy both using it on the same rows, it also fastens up because you only compute it once for each upvote/downvote, instead of once or twice for each post detail request.