2

I am using Yii framework and I have a Post that has many comments, I need to get a list of posts, and each post should hold the latest 5 comments with it.

  1. First Option: Is to make a join between posts and comments table and in the PHP code I should normalize the posts list to remove the duplicates and put the commets inside each related post.
  2. Second Option: Is to load the posts list, then in PHP code iterate over these posts list and load the comments for each post using a separate sql hit.

Which one has the best performance and is there any better way to do it?

Mahdi Hijazi
  • 4,424
  • 3
  • 24
  • 29
  • 1
    This sounds like the old Top N per Group problem. [This question](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group) and others linked from it might be helpful. – DCoder Jul 15 '13 at 04:50

1 Answers1

0

You should never have incremental hits on your database because of the data. Therefore, the first option would be the wisest one. If you do a join and then filter away the stuff you do not need, your program will be a lot faster than if you do one more database lookup for each row the previous query returned.

For more information, have a look on lazy and eager loading here: http://www.yiiframework.com/forum/index.php/topic/34412-eager-loading-vs-lazy-loading/

Victor Bjelkholm
  • 2,177
  • 9
  • 28
  • 50
  • 1
    yes, I agree with this answer, loading all data in one query is much better than second option. –  Jul 13 '13 at 22:05
  • thanks for pointing me into the right direction, also I think this article will be helpful to apply the above concept using Yii framework http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation/ – Mahdi Hijazi Jul 14 '13 at 22:23