Due to historical legacy issues, the index structures of some WordPress tables are not optimally designed, and it is almost impossible to modify them in future updates, which has also caused slow query problems when dealing with large data volumes (100,000+ entries).
Fortunately, someone has developed a fix plugin for this issue: Index WP MySQL For Speed
The usage is very simple. After installing and activating the plugin, first back up your database, then check all the checkboxes and click apply.
If your website has an extremely large data volume (million-level), it is recommended to extend the timeout settings for PHP and Nginx before performing the operation to avoid mid-operation timeout interruptions (or use the cli command provided by the plugin).
WordPress may also modify the database after a version update, so you may need to repeat the operation after updating the WordPress version.
