DB Performance and EXP_RELATIONSHIPS table

I’ve seen a few posts related to the exp_relationships table and performance, but not a lot of detail yet.

I’ve just joined a site that has ~45K records (don’t let this distract you) in the exp_relationships table. We regularly see ‘UPDATE exp_relationships …’ queries in MySQLs slow_query log. Without table locks and other interference these queries can still take between 7 - 30 seconds (and occasionally much more) to complete.

I think this is inherently an issue of scale. Most of the queries we see in the slow query log need to update 100+ records in the exp_relationships table, the more records to long it takes. The serialized php record in rel_data averages around 30Kb and across ~5000 records this equates to about 180Mb that needs to be written to disk (and not in any single place).

Does anyone have some more information on what is stored in the rel_data table, and importantly (for me) why it’s possible for ee to cache so many copies of the same data?

I’m going to look at ways the DB could be optimized (indexing etc) to help improve this, but my initial thoughts are that this is going to be inherently slow just because of data volume, spread across the table. Thoughts?

Thanks