argh, the engineer in me says yuck…

the pragmatist says yes, we should do that…  almost every other forum program (large) I have seen ends up doing the same thing for the very same reason.

Go for it…

I think you will find (as I have seen in other forum apps) there will be other similar tradeoffs to be made.

Yes – it hurts doesn't it? Goes against all I have preached in the past. But with the exception of the later 'taxonomy' stuff, the WP database is horribly flat as it is and quite obviously designed by someone who knew nothing about good database engineering. So we will be in good company!

I am starting this project with the queries that (a) are called most often and (b) take the longest time. There are going to be other posts like the above as I isolate the victims!

just let me know where to stay clear of to prevent potential conflicts…  time permitting tonight, will work the initial subscription stuff…

Actually – this task you have to reduce queries in the permissions section? Would be really useful if you can get that one out of the way as soon as possible as I suspect that will inflate some of my stats… Is it an easy change?

OK – a few notes and requests.

In the end I did NOT make the changes above as I took a different approach. Using the quite wonderful 'wp-tuner' plugin and simple query counts, I concentrated efforts on (a) the most costly queries in terms of time of execution and (b) mutiple calls to the same table during a forum page load. This actually turned up teo sorts of issues.

The worst case was just sloppy thought processes. A page of posts (8 posts in the topic) was making 67 calls to the sfmembers table! Yes – 67. The reason this was particularly bad was because the overall query that populated the posts INCLUDED sfmembers table and to eimate those 67 just needed the sfmember columns adding to the query field list!

This is the important bit:

I have changed the way virtually all views grab their data. My goal was to elimiate as much as possible queries being run on each row (i.e., topic entry in forum list, post entry in topic etc) as these tend to be the most expensive. One larger, more encompassing query to pre-collect as much data as possible turns out to be more efficient in all aspects to multiple little queries getting repeatedly called. There were one or two I could do nothing with (post rating was one as reported in the ticket because the sfpostratings table has no topic id recorded – add that and we could improve its performance).

What we need to keep doing in the future:

Evaluate and examine each and every query. Re-running a query in every display row needs to be avoided wherever possible., This should be a last resort. Designing data structures that can be returned to satisfy all rows should be the goal.

If it MUST be on every row – then at the very least cache results if it makes sense to do so.

not sure we can add the topic id to post ratings at this point.  can we recreate the data after the fact?  dont know, but can certainly look into…