ilhan
ilhan

Reputation: 8995

Too many queries for selecting hierarchical comments

I have this MySQL table but it takes one query for one comment. If there are 20 comments it makes 20 queries to show the page. Is there any solution? Is it possible to write a MySQL-side function in order to reduce the query size to one?

Upvotes: 3

Views: 215

Answers (3)

Jon Black
Jon Black

Reputation: 16559

You can do it in a single call from php to mysql if you use a stored procedure. I'd stick to the adjacency list vs. the nested set implementation as you'll only experience more pain using it.

see here: Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

Hope this helps :)

Upvotes: 1

carpii
carpii

Reputation: 2001

You could look into Joe Celko's 'Nested Set' algorithm. It provides very efficient 'one-query' retrieval for hierarchical datasets, but there is always a cost, and the cost is that it requires a bit more legwork when you insert into the table.

For high-write activity, I'm not sure I'd go for it personally.

I'd be more likely to just slam it into memcache, and invalidate the cache when someone posts to a specific thread.

Either of these solutions though, is way better than running 20 queries to retrieve 20 comments.

Upvotes: 2

Amber
Amber

Reputation: 527063

In addition to storing parent, also store (in a separate column) an id for what item/article the comment was posted on. Then just query for all of the comments with the same item id, and construct the hierarchy after getting the DB rows.

Upvotes: 5

Related Questions