5 ms·

Why not just store the parentid (can be another comment or the news topic), username (instead of userid so you don't have to access the users table), and voteco

by vlad 19y ago

Why not just store the parentid (can be another comment or the news topic), username (instead of userid so you don't have to access the users table), and votecount for each comment? Also, each comment should have a newsid field that points to the original newstopic. The left-most posts would have the parentid and newsid that were the same (or parentid can be zero, depending on how you do it). But any replies would have the parentid to the parent comment, but the newsid would still point to the newstopic. Then you just run an SQL query to find all comments with the newsid for that newstopic. You won't have to join any tables. After that, with everything in memory, you can just sort the way it has to be. Finally, you can cache the page in memory or a file in case the page doesn't change for the next set of visitors.

nostrademons 19y ago
How do you get the full thread for a given parent? Selecting based on the parentid alone gives you only immediate descendants. Recursive queries are generally a no-no, as they drag your performance down fairly quickly. Selecting based on the newsid gives you every single comment on the item, and then you'll have to do a lot of work (basically building up the full tree structure in memory) to find out which are children. For a site with traffic like news.yc, it's probably acceptable, but it's still more complicated to code than a simple select.
- vlad 19y ago
  You would find the full thread by searching for comments with the appropriate newsid. To simplify the recursive function (using data in memory, not in the database), you could also have another field, called indent, that stores the level in the comments. You could find all the comments with one SQL statement (where newsid = news.id), then go through each level of each comment, and sort it that way. You could even make the parentid field a double, so you would store 123.4 where 123 would be the parent and 4 would be the indentation level. I guess the difference is, do you want to store everything in one field or have different fields for everything? I think this gives the original poster many options to think about. To me, a recursive function would be easier to write than coming up with ways to combine multiple fields into one using MySQL. I think the order of optimization is: 1) Optimize so you can use a cached result if it exists 2) If the cache is out of the date, try to make just one SQL call 3) don't use table joins 4) reorder the data as needed in memory, then save it to a cache As far as the single SQL call goes, I don't think it matters whether you have 5 fields or 30 in the actual table as long as you only request the fields you need, but I could be wrong. By the way, do you have any other interesting examples?
  - deleted 19y ago
    [deleted]
  - nostrademons 19y ago
    Interesting examples of what?
  - clueless 19y ago
    i'm not sure why nobody has said this already... but why would you need an extra filed for keeping indentattion nuber when you can use <pre> in html for that.

by vlad 19y ago

nostrademons 19y ago

How do you get the full thread for a given parent? Selecting based on the parentid alone gives you only immediate descendants. Recursive queries are generally a no-no, as they drag your performance down fairly quickly. Selecting based on the newsid gives you every single comment on the item, and then you'll have to do a lot of work (basically building up the full tree structure in memory) to find out which are children. For a site with traffic like news.yc, it's probably acceptable, but it's still more complicated to code than a simple select.

vlad 19y ago
You would find the full thread by searching for comments with the appropriate newsid. To simplify the recursive function (using data in memory, not in the database), you could also have another field, called indent, that stores the level in the comments. You could find all the comments with one SQL statement (where newsid = news.id), then go through each level of each comment, and sort it that way. You could even make the parentid field a double, so you would store 123.4 where 123 would be the parent and 4 would be the indentation level. I guess the difference is, do you want to store everything in one field or have different fields for everything? I think this gives the original poster many options to think about. To me, a recursive function would be easier to write than coming up with ways to combine multiple fields into one using MySQL. I think the order of optimization is: 1) Optimize so you can use a cached result if it exists 2) If the cache is out of the date, try to make just one SQL call 3) don't use table joins 4) reorder the data as needed in memory, then save it to a cache As far as the single SQL call goes, I don't think it matters whether you have 5 fields or 30 in the actual table as long as you only request the fields you need, but I could be wrong. By the way, do you have any other interesting examples?
- deleted 19y ago
  [deleted]
- nostrademons 19y ago
  Interesting examples of what?
- clueless 19y ago
  i'm not sure why nobody has said this already... but why would you need an extra filed for keeping indentattion nuber when you can use <pre> in html for that.