CODEWITHSUNDEEP
phpmysqloptimization
Pablo
Pablo

Reputation: 6058

Counting rows in a SQL query vs updating a field with PHP

I'm working in a commenting application and i would like some feedback on the method that i am using to keep track of the number of replies or likes that a comment has. Comments and replies are stored in the same table, to determine if a comment is a reply i use the field parent_id if it is anything other than 0 the comment is a reply.

Please note that i wont be including all the columns of the table below:

 cid | parent_id | replies | likes 
-----+-----------+---------+-------
  2  |     0     |    3    |   0   
  3  |     2     |    0    |   0   
  4  |     2     |    0    |   2   
  5  |     2     |    0    |   0

In the table above comments with id (cid) [3,4,5] are replies of comment #2. The columns replies and likes are integer that hold the count of replies and likes accordingly. The integrity and accuracy of these columns is maintain and updated through the PHP code, for example if another reply for comment #2 is added than the replies column would be increased by one or decreased by one if deleted.

Im also aware that i could dynamically calculate the replies count in the SQL query that fetches the comments but i thought it would add more stress to the SQL server. This query would look something like these:

SELECT cid, parent_id, (
    SELECT count(*)
    FROM comments as SC
    WHERE RC.parent_id = C.cid
    ) AS replies
FROM comments AS C
WHERE thread = {thread_id}

Am i doing it right by storing the replies and likes in an actual column in the table? or am i exaggerating about the stress that a query such as the one above would have in the MySql server and i should use such complex query instead?

Any feedback would be appreciated, thanks

Upvotes: 1

Views: 99

Answers (1)

Karthik
Karthik

Reputation: 1396

I dont think you need the column called 'replies'. Just occupies additional unwanted space. Do a combined Index on cid and parentId. That should be good enough. Queries should be fast. By having the column, you are adding more stress to app code & mysql. (App code for maintaining integrity and mysql coz 2 writes in the place of 1 write - when a comment is entered).

But if you are talking about millions of rows, i wouldnt choose mysql for it, rather mongo, the data can be constructed as a beautiful JSON and dumped in mongo.

Upvotes: 1

Related Questions