SQL queries to optimise the Flarum database

Oshvam

I've been wondering for a while if there are any predefined queries in Flarum to optimise the database. For example, for WordPress we have queries like this one, which deletes some orphaned data:

DELETE wp_postmeta FROM wp_postmeta
LEFT JOIN wp_posts ON (wp_postmeta.post_id = wp_posts.ID)
WHERE (wp_posts.ID IS NULL)

However, for Flarum I can't find anything that can help locate unnecessary data or that can be optimised, so to date the only maintenance I'm doing on my sites is scheduling a generic optimisation via phpMyAdmin.

Maintaining a clean and organised database, free of unnecessary data can really speed up site performance, I think it's a healthy thing to do. I thought I'd open this thread if anyone has or wants to share some useful queries, something like :

DELETE FROM posts
WHERE discussion_id IS NOT NULL
AND discussion_id NOT IN (SELECT id FROM discussions);

clarkwinkelmann

Flarum used InnoDB integrity constraints for the database from the get-go, so orphaned data like you describe should not exist unless some manual changes were made to the database directly while disabling the constraints. I assume this happens in WordPress because they don't use constraints to support more database engines.

The only things I can think of:

Delete users that have never been activated.

Delete settings key and permission entries for extensions and tags that no longer exist. This probably won't have much of a performance impact though.

Delete posts and discussion that were created using the private API by an extension you don't plan to enable again, since those posts and discussion effectively become invisible forever if you permanently delete the extension. There are very few extensions that do that though or you would be unlikely to delete them forever.