search

OllieJones / index-wp-mysql-for-speed

A plugin to add useful indexes to your WordPress installation's MySQL database.
GNU General Public License v2.0
117 stars 10 forks source link

Index BuddyPress tables. #13

Open OllieJones opened 2 years ago

OllieJones commented 2 years ago

A user asked for indexes on BuddyPress tables.

https://wordpress.org/support/topic/index-buddypress-and-other-meta-tables/

More information is needed, about the bad queries.

rjasdf commented 2 years ago

Crude diagram: https://buddypress.org/wp-content/uploads/15/2013/11/bp_db_schema_v1.0.png Need CREATE TABLEs Need queries I see that some columns are VARCHAR(200), and are probably simple alphanumeric. Can we ask BP to either drop the 200s and 255s to 191? And explicitly call hashes CHARSET ascii COLLATE ascii_bin (or whatever is appropriate)?

nickchomey commented 2 years ago

I'm interested in helping with this issue. I use BuddyBoss, but it is largely the same as BuddyPress (and also includes bbPress and more).

Please let me know what I can do to help!

OllieJones commented 2 years ago

Thanks, @nickchomey . We really need your help! (Sorry for the slow response; I should tell Github to ping me when people offer to help.)

Good first step: Install the https://wordpress.org/plugins/index-wp-mysql-for-speed/ plugin on a buddypress / buddyboss / bpress site and then do two things:

1) go to the About tab under Tools / Index MySQL and upload the site's metadata.

2) go to the Monitor Database Operations tab (https://www.plumislandmedia.net/index-wp-mysql-for-speed/monitor_database_operations/) and start a monitor. Maybe run it for five minutes or so. While it's running, visit some of the pages (both backend and frontend) that are unpleasantly slow. When the monitor is done, please visit its tab and Upload the saved monitor.

Please reply here with the upload ids so we can find them.

And, of course if you know about slow queries from using Query Monitor, please reply here and tell us about them.

Thanks again.

nickchomey commented 2 years ago

Thanks!

However, i've realized that I probably can't be all that useful right now as my site is still in development thus a) doesn't have any users nor much data and b) has a lot of moving pieces with different plugins being used and not used.

Also, buddyboss has been making a lot of big changes (adding static variables and object caching) to their code lately, and should also be releasing a new version of their theme soon. So it probably makes more sense to wait for all of that before trying to optimize anything.

Also they do seem to have a bunch of indexes on their tables (though don't see to have any compound primary keys), so it might be worth installing the buddyboss platform to take a look at their database structure and see if you can take anything from it to improve buddypress.

I uploaded metadata just now with the id m9C5E8gB for your reference. When I have launched my site and have data and notice slow pages, I'll definitely let you know!

P. S. You should be able to set your notifications on the Github repo to All Activity

femio1 commented 11 months ago

Just wondering if there has been any advance on this issue of indexing for Buddy Press / Buddy Boss. I use the latter and always in the hunt for improving the set up.

nickchomey commented 11 months ago

As it turns out, buddyboss is overhauling their database tables and general architecture right now. I haven't had a chance to look at it yet, but the version released the other day made changes to the activity feed tables, and the next few releases should have a lot of other changes to code and maybe the db.

The most impactful thing, however, is getting a good server. With one of Hetzner's new dedicated servers, you can get 2x the performance of the commonly touted vultr hf or aws ec2 (though aws they released new servers recently that use latest Gen cpus). You'll also need a good server config.

All of this has been discussed ad nauseum in the various buddyboss Facebook groups.