Performance thoughts?

I just upgraded wordpress to the latest version, which involved adding a key to the posts table. This query had a table-level meta lock, and took 15 minutes to complete, so that hung the site for visitors as it parsed through everything (and perhaps some bad queries – I never understand why plugins have so many database writes when a non-logged-in visitor is simply viewing the site…)

Anyway, I’ve been looking to reduce the size of the posts table and am erasing all of the revisions of the posts – this site is a large site and has been around for a couple decades, so has built up a lot of posts and revisions. Hopefully that will make a big difference, but in the process, I noticed the database structure of your plugin, and wonder if it would be better to have your own table, rather than having the (basically 28,000 empty) rows in posts and then roughly 3*28,000 entries in postmeta).

It seems to me (on a 15 second glance at your plugin), that you could use far less data, and then not have any performance impact on all of the queries that access the post and postmeta table, but creating your own table:

related_posts (id, parent_post, child_post)
or something like that? I think I could probably write a db hook/filter that traps your database reads and writes and redirects them to a table that I create myself, but if you would make the change on your end, then all of the users would get the performance impact, and I don’t have to worry about needing changes if/when you update the plugin.