I'd have a problem with dumping it.
There is a massive amount of knowledge and wealth here. There are countless solutions to problems that people have had and some very interesting discussions that everyone should read at least once. There is no doubt also a lot of crap, but it would be a shame to loose all the good stuff just for the sake of keeping everything ship shape.
Recently we've been getting quite a few hits from search engines for people looking for solutions to their problems which is nice to see. Although I'm not sure how the managed to find us, because I've tried searching for things I know are here and we never show up, but they'er definitely finding us. I'd like to think the posts they find are useful as well.
I totally understand where you're coming from, but also look at all the shite I've got stashed in my loft/shed/garage!
Why not look at getting a static HTML archive site for it all then? Would that be a suitable compromise?
:O)
HTML wouls stop old posts being archived, therfore stopping the need for intense queries... I'd imagine.
While I couldn't help.. I can donate my PC and bandwidth for conversion purposes if needed in some way.
Whenever this happens, I'm all for archiving it.
I know you've explained this, but I think I didn't understand the explanation. What's the problem (in terms of performance) in making this forum read-only and then starting afresh?
I mean, I realise the archived forum would still have the same performance issues but surely it would be used so little as to not matter? It would be used as much as people use the search function currently, at most. Which for me, is very very seldom. Regardless, the overall load should be much lower shouldn't it?
If for some reason that's not practical, how about dumping this database, getting someone to host this forum read-only somewhere and again, starting afresh here. The archive might fall over a bit but... better that than this place suffering. The slowness is not the fault of the software or whatever, just the size of this place. Butif it's keeping people from posting then we need to make sacrifices I think.
if (looking_for_unread_messages && $_SERVER['LAST_BH_MESSAGE'] > $_SESSION['USER_LAST_THREADLIST']) { // perform query as normal } else { // do cut-down query or cached query or whatever }
Is there any way of compromising somehow? This feels like wishful thinking but I do not know. Anyway - somehow semiarchiving old stuff so it isn't actually affecting anything except on the rare occassion someone isn't going through old threads. Hmm, no, this doesn't sound likely. Hm.
having it all separate and readonly feels wrong somehow.
I'm not entirely sure if I know what I'm talking about but would it not be possible to periodically move posts/threads etc. that are older than a certain amount of time (say a year) to a seperate table (or tables) which are then only ever searched when absolutely neccessary?
So regular thread list 'stuff' would only look at the 'current' table(s) wheras the Search might look at both the 'current' and the 'archive' table(s) using whatever SQL magic that requires.
Any time a post is retrieved it would compare its number to the 'last-archived-thread' and based on that decide which table(s) to get it from. For the most part it would only require recent posts so only ever do stuff with the leaner 'current' table(s), thereby making everything faster, finding a cure for AIDS and achieving world peace.
Does that make sense? Am I repeating people? Can I go home yet?!
Hmm. But then posting in a thread from 3 years ago might cause problems, or would it? I don't know.
I think the main problem with that is it would be hard as fuck to make. And would pretty much be a one-off. So no one will make it.
And also the script which does the splitting would have to be run as a cron job or something like that. And would be, I reckon, slow as fuck.
(That's not to say it's a bad idea, it would be the ideal solution, just I don't see it happening)
I think we have three choices:
1. something like you suggested
2. carry on as we are
3. start again and archive the rest somewhere
I think 1 won't happen and 2 shouldn't happen so I like 3.
Hmm. I wasn't really thinking of it as anything more than a one-off. If it would be a useful feature generally then, aye, makes it worth it.
It'd still take ages to execute here and that is a problem.
As for complexity - yeah, it could be done as you say but... why bother? That offers no advantage over just making this forum read-only and starting again. Well, little advantage.
If it were to be done properly it would happen in a sort of rolling way. And that would be hard. By which I mean not having to run a script to do the archiving but mechanisms put in place whereby older posts (beyond an arbitrary threshhold) aren't involved in queries. But... I have no idea how that might be done.
But aye, if you're up for making it then that's cool by me, of course.
I'm just looking at the message SQL with my optimizing hat on, and there's quite a lot that could be done to reduce the load on the database there.
The SQL includes a join to the THREAD table from every post, which is redundant. The values from the THREAD table can be retrieved in a single SELECT before the message SQL is run.
There are four joins to the user table, one of which (APPROVED_USER) is only relevant if posts have to be approved by an administrator, and another (EDIT_USER) only if the post has been edited by someone other than the original author. The APPROVED_USER join could be added in conditionally, and the EDIT_USER could be done using singleton SELECTs after the main select is finished.
This one might be a stretch: there are two joins to the USER_PEER table for the relationship and the new peer nickname thing. It might make more sense to do a single select against the USER_PEER table for the logged-in user and cache the result in an array, then get the values from that. It kind of depends on how many peers people have, on average. I've got 23, mostly ignoring sigs. Some testing might be advisable for this one.