About core integration, it’s complicated. Many hosting providers use ancient end-of-life versions of MySQL. Those versions need different keys than more modern versions. So, core would end up with two different versions of the WordPress schema, one for legacy DBMSs, and another for modern DBMSs. That’s not viable: they have enough to test with each new release as it is. Read this and weep. https://www.ads-software.com/about/stats/
About BuddyPress: You are correct that there are indexing opportunities in the wp_bp_ tables. I don’t have access to a big BuddyPress site. If you do, please use the plugin’s Monitor feature to capture some database interactions, and upload the monitor. I’ll take a look. It’s best to design indexes.
Thanks for your feedback and your interest!
