Combining Threads, Posts, Members queries on forumhome into one?

[N9ne]

This should reduce queries, maybe make forumhome faster, less server load...etc.?

How can I do this then...:D

[NTLDR]

If you mean the post/thread counts then I'm not sure if you can do the counts from 2 different tables in one query.

[N9ne]

I mean at the top, Members: 5000, Threads: 6000, Posts: 525252

That part ???

[NTLDR]

I doubt that it can be done and if it can I expect its less load on the server to do it the way it is now.

[N9ne]

ok thanks :)

[N9ne]

Do you have any other techniques of reducing queries on forumhome, forumdisplay, showthread?

[Logician]

If you are patient enough to go through the entire vb structure and hack some parts accordingly, here is a thought:

Instead of querying the database online when that info is needed, save this info in a seperate record alltogether and query that field only when needed. Let me clarify:

Your main page querys your db for member count, post count and thread count each for one, 3 in total. If you update a table field with this info everytime membercount/postcount/thread count changes, you can just query that field and get all 3 info at once with a single query. But of course you need to hack vb so that everytime this numbers changes, your record is updated.

This method applies to all queries in you main page. If you go to extremes, you can make it so that your forum home query is 1 in total, getting the entire page as a HTML code with one signle query, provided that you can manage to update that code everytime an incident that changes your main page happens. Well this is of course way too radical, but I just wanted to demonstrate the idea..

You can check the buddy and ignore columns in user table for a "live demonstration". It's only one field so the data is get with 1 query, but they save more than 1 records in it.

[N9ne]

Would functions.php be the file to edit to do the above you said, ie. combining those three queries?

[Xenon]

no, that's what he meant, you have to change more than just one file...

you have to change register.php for the meber stuff
and editpost.php/postings.php/newreply.php/newthread.php for post / thread stuff ;)

everytime you add/delete a post you have to change the posts-value in the db....

[N9ne]

I see, with my tiny knowledge of PHP, I'll stick with my queries for now ;) thanks for the insight though!

[Xenon]

hehe, just found out a way to combine the thread an postcount queries!!!! :)

in your index.php you find this:// get total posts
$countposts=$DB_site->query_first('SELECT COUNT(*) AS posts FROM post');
$totalposts=number_format($countposts['posts']);

$countthreads=$DB_site->query_first('SELECT COUNT(*) AS threads FROM thread');
$totalthreads=number_format($countthreads['threads']);


replace it with this:
// get total posts
$counters=$DB_site->query_first('SELECT SUM(replycount) AS posts, SUM(threadcount) AS threads FROM forum WHERE parentid=-1');
$totalposts=number_format($counters['posts']);
$totalthreads=number_format($counters['threads']);

if the thread/post ammount is incorrect you have tu update your counters in your acp first ;)

[N9ne]

Thanks for that Xenon, but above NTLDR said that it would be less server load with how it is now [an extra query], is this true or will your fix not put more load on the server?

[Xenon]

well i can't say exactly, but i applyed it on my page and, the mysqltime goes down, so i think you can use it :)

[N9ne]

Ok thanks Xenon :)

[N9ne]

Do you have anything for showthread and forumdisplay?

[Xenon]

welcome :)

no, not yet for one of these files, have had some good ideas, but they are a bit into the direction of what logician said, so i canceled the idea because upgrading is so hard with such restructurizations of vb-files ;)

but i think TECK has had some good optimizations hasn't he?

[N9ne]

He has indeed, but it's too hard to get a hold of him;)