• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Improve Topic Counts for Forum?

#1
[eluser]nullsys[/eluser]
Hi all,
Just a quick one I'm hoping you can help with.
I'm considering the "best" way to count the total topics within a category, all stored in MySQL.

Schema:

Categories
----------
id, name, description

Topics
----------
id, cat_id, subject

There is a relationship between topics.cat_id and categories.id

I'm currently using:

Code:
$id = 1;

public function count_all_results($id)
{
  $this->db->where('cat_id', $id);
  $this->db->from('topics');
  return $this->db->count_all_results();
}

This is working well, but I can't help think there is another "better way" because I may also want other data like "Last post by:" or "Last Topic:".
1. Storing the number of topics along side the category data in the table "categories".
2. Using another table with just topic and post counts.

Any suggestions would be greatly appreciated, thanks!

#2
[eluser]InsiteFX[/eluser]
All you nee to do is this in your controller after getting your query.
Code:
$topic_count = count($your_query);

#3
[eluser]nullsys[/eluser]
I'm sorry but I don't understand your reply at all. I don't see how it fits.
Perhaps we have some wires crossed Smile

The above code is being called as a way to list the amount of topics in a category for my forum.

I'm simply looking for a way to refine the code, or perhaps the layout of my database so that it's less intensive, or requires less queries.

For example, should I be updating a "topic_count" field in the "categories" table when new topics are created/deleted.
Or, should I stick with what I have?

Either way, it sounds like the same amount of queries.

#4
[eluser]WanWizard[/eluser]
From a performance point of view it would be better to store it in a column of the forums table (and increment that counter when a new topic is created) then to query the DB every time.

Alternatively you could create a forum cache and store it in there. That would also remove the forum table query, giving you more performance.

In general, cache what you can.

#5
[eluser]nullsys[/eluser]
Thank you!
I'll go down the cache route.
If you know of any recent tutorials on the subject, I'd appreciate the help.
Cheers WanWizard!

#6
[eluser]WanWizard[/eluser]
Look at the cache drivers in the user guide (not to be confused with output caching).

Greg Aker wrote about it, so did Jamie Rumbelow.

#7
[eluser]boltsabre[/eluser]
As mentioned caching is a good thing, but how you'll go about getting the data to cache will depend on a few things, and it'll be up to you to do your own testing to see what is quicker.

Without knowing your complete DB forum table schemas, the amount of data stored in your tables, the number of hit's your getting a minute, the speed of your DB server, etc, we can only speculate which method will be quicker.

The two obvious solutions are:
1. As WanWiz said, create a new column in your category table called "total_topics" (or something similar) and increment it each time there is a new topic, and de-increment when a topic is deleted/archived. (But this means each and every time there is a new topic, you're also making a new 'update' query to the category table to write the increment, how much does this slow things down for your users?). Then whenever your cache updates (every 5 minutes for this?) it just needs to get the 'total_topics' from your category table.

2. Create a new column in your topics table called deleted (tiny_int, length 1, default 0, indexed), and whenever a user deletes a topic set this value to 1. Then to get your total_posts your sql would look something like
Code:
$this->db->where('cat_id', $id);
$this->db->where('deleted', 0);
$this->db->from('topics');
return $this->db->count_all_results();
This has the advantage to the user/your db that every time there is a new topic you're not updating the total column count in your category table as mentioned in the 1st suggestion above. However, it does mean that when you are getting your total count for your cache it won't be as quick as in the above scenario.

So... it's a bit of a trade of... more DB hits but a quicker count for your cache (scenario 1), or less DB hits but a slower count for your cache (scenario 2). As I said, you'll have to do your own testing to see which will work best for you and your users.

Also... lets say you have 20 different categories, and you have a page that lists all of them with their counts, one would assume that scenario 1 will reap the most benefits as you're just getting the details from 20 different table rows (as opposed to number 2 which would involve a querying your entire topics table with a COUNT, a GROUP to get the cat_ids grouped together and a WHERE clause to figure out which are deleted). But if you're not listing all 20 together (just listing one at a time on separate pages) then number 2 might be the best way to go... just depends on your requirements, you're tables, current and expected amounts of data, server speeds, etc. Some food for thought - time to get testing!!!


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.