0

i dealing with a case that i allow every topic to have only 100 lasts comments

in case a topic have already 100 comments , and a new comment i coming i want to delete the very first comment in the 100 comments chain and add the new one , here is an example :

1,2,3 .... 99 , 100

2,3,4 ....100 , 101

as you can see the very first comment that was far behind the others got deleted and the new one got in the 100 comments chain

here starts the problem , if i have in my case forum , and this forum have hundreds thousands of topics , it can reach millions of comments , which means if i check how many comments there are with every new incoming comment it will cause slowing the site with every comment adding , how can i minimize the data base query's ? is there any system / known ways to facing that kind of things?

Community
  • 1
  • 1
homerun
  • 19,837
  • 15
  • 45
  • 70
  • 1
    I don't think you should restrict or delete something if you have problems with your performance, it can't be way out. Try to find what's concretely slowing your app and optimize it firstly. –  Oct 18 '11 at 14:53
  • 3
    You might want to consider not deleting the comments but just displaying the last 100 comments. – JJJ Oct 18 '11 at 14:54
  • 1
    assuming that you have a field called `ts` whoch stores the timestamp of the comment when it was added . let that column be indexed in the database , then do a select * from comments where .... order by ts desc limit 100 ! – Rami Dabain Oct 18 '11 at 15:01
  • I agree with user973254 and Juhana, deleting comments is probably not the best idea. How about creating a separate table which can only hold 100 comments and kind of implementing what MarcB mentioned below? where you delete the oldest comment the moment you reach 100 count. than you only have to refer to this table each time you need to get top 100 comments. – Nertim Oct 18 '11 at 15:01

2 Answers2

2

Why would you delete old comments? If you want to show last 100 comments, then just SELECT id, thread_id, user_id, comment_body from COMMENTS where thread_id = @thread_id LIMIT 100. Also be sure to have indexing on foreign column so that it gets queried fast and query only columns you need.

And no, if you are going to be wise with queries, apply indexing where needed then you don't need to worry about each comment slowing down the database. Will you have millions of millions of comments? If so, you can think about partitioning the database say every 1000000* threads based on thread_id.

You may be interested in this question: Database architecture for millions of new rows per day

* For anyone who reads: Don't assume this number as some advice or suggestion out of experience. Never say: "someone on SO mentioned this number x, so..." I have no experience or benchmarks to say that it would be good to do it at this number. I'm only creating my first partition myself. Evaluate yourself what is good for you.

Community
  • 1
  • 1
Janis Veinbergs
  • 6,907
  • 5
  • 48
  • 78
  • 1
    Good advice. Even if you didn't want to partition and really didn't want the older comments in there, you could execute a regular job that archives / deletes all of the older comments. Don't include the logic to archive / delete the comments in with your insert, just let that add the new row and be done with it. – Kasaku Oct 18 '11 at 15:04
1

What's your database structure for this stuff? Presumably you have a "posts" table, and a "comments" table, which links back to the posts table. Assuming decent design, the comments will have an auto_increment primary key, so your logic would be:

1. insert new comment
2. count the comments attached to the post. If it's > 100, then
3. find min(comments.id) and delete that ID.
Marc B
  • 356,200
  • 43
  • 426
  • 500