Reducing huge mysql database size?

[maoz440]

My forum's database (mysql) is almost 6 gigs - huge in my opinion. Is anyone else's this big? Any ideas on what the optimal database size is? We have 400,000+ posts. Any ideas on how to trim this down considerablely would be greatly appreciated!

maoz

[Xenon]

1. well, adjusting the badwords.php can help

2. don't allow attachments.

3. increase the minimun letter count of words to search for...

after 1 and 3 you have to reindex the searchindex

[maoz440]

Thanks for the speedy response Xenon! How hard would it be to go into phpmyadmin and delete all attachments over 60 days old in certain forums? I am still learning phpmyadmin and am a little leary of it still.

maoz

[Xenon]

DELETE FROM attachment WHERE dateline<xxxx

just replace the datline with the unixtimestamp of a day 60 days ago ;)

this would delete all attachments older than that date, but if you want it just in a specific forum, it would be harder, you'd have to write a script which gets the postids of those forums and so on and so on, just with phpmyadmin i think it isn't possible

[Kars10]

Another small help for you: prune inactive users and delete old posts. :)

[Xenon]

damn, old posts and i forgot to mention my archiv hack which compresses old threads in the db :)

[Dean C]

Cutting down on spammers can help too. And removing any unnecessary profile fields. Disabling the calendar in the ACP. The list goes on. Disabling Private Messages is the best way IMO :D

Xenon - how can adjusting badwords.php help :D??

- miSt

[Xenon]

Mist, just edit the file, and you will see ;)

there should be words in it, which are often on your forum, that's why it should be translated if you have a nonenglish board

[Dean C]

Ahh i see now ;)

- miSt

[maoz440]

Originally posted by Xenon
DELETE FROM attachment WHERE dateline<xxxx

just replace the datline with the unixtimestamp of a day 60 days ago ;)

this would delete all attachments older than that date, but if you want it just in a specific forum, it would be harder, you'd have to write a script which gets the postids of those forums and so on and so on, just with phpmyadmin i think it isn't possible

Would running this query, and thus deleting all attachments older than 60 days screw up the database or the forums in any way or is this the only query/step I would need to perform?

Thanks! :D

[Xenon]

ups, you have to run also this query then:
update post set attachmentid=0 where dateline<....

you should update your thread counters afterwards so the attachmentcounter is correct again, but nothing more :)

normally everything should be perfect then, but i can't promise, i've never done that ;)

but your forums and db would work afterwards :)