Cleaning up the Database

[italymadeeasy]

Hi there, with more than 8000 students we have accumulated a lot of "comments" and "user activity" entries in our database. since answers to quiz questions and assignments are "comments" you can see how quickly these add up. We have tables with nearly 100,000 rows. What can be safely deleted from the Database without affecting user grades and other important things? Cheers

[logan]

Hello, you could delete only questions comments but not assignments.

[italymadeeasy]

Thanks! Are "questions" easily marked in the database (I am not the expert and would need to ask my developer)? Cheers

[Mk]

Well you have to do something like this : $wpdb->query($wpdb->prepare("DELETE FROM {$wpdb->comments} WHERE comment_ID IN (SELECT * FROM {$wpdb->comments} as c LEFT JOIN {$wpdb->posts} as p ON c.comment_post_ID= p.ID WHERE p.post_type=%s)",'question'));   as this is a delete query you need to carefully test and then run this .

[italymadeeasy]

Thanks!

[Mk]

Please use it carefully. If you find any other issue please don't hesitate to ask. Thank you.

[MeesterGijs]

Dear @italymadeeasy, I read your post from over a year ago about this same issue. I now have 2000 students enrolled in all courses and tables of over 520,000 (wp_usermeta), 330,000 (wp_postmeta) and 300,000 (wp-bp_activity) rows. Do you have any advice for me? More expensive hosting with more CPU and RAM? Or build a new website from scratch without wordpress or plugins? I'm curious to get your advice. My site is: meestergijs.nl Thanks, Gijs

[MrVibe]

We have build a tool for this in WPLMS it self. Please check WP ADmin - LMS - Settings - Admin functions - Delete old question comments. https://prnt.sc/paczs1 As we're caching the quiz results in activity meta, there is no need to saving comments to questions whose results are already cached.

[Author]

Posts