How Do I Use Mysql To Retrieve All These Posts?

Yii 1.1 General Discussion
1

I would like to know what is the best way to retrieve post messages with their comments.

I have a Post, Comment, And Rank table. Each containts information such as :


Post

----------

post_id

post_title

post_tms

post_body

post_owner

post_status


Comment

---------------

comment_id

comment_on_post_id

comment_owner

comment_tms

comment_status


Like

---------------

like_id

like_on_post_id

like_owner

like_status

How can I get retrieve the posts with their comments and likes, where it doesn’t

cause so much overhead on my server ? Assuming each page has 50 posts.

2

The answer depends on how many rows you have in your database.

If you have millions of Posts with hundreds of comments/likes per Post, then you might want to use "lazy" loading (where you retrieve the 50 posts you are going to show then retrieve the comments and likes for each post).

You probably don’t have that many rows, in which case you can just do a query that joins the 3 tables.

3

BTW - either way you retrieve your data you need to make sure you have indexes on comment_on_post_id and like_on_post_id.