craftcms
[4.x]: Slow CP entry/user searching 8 seconds+ (AfterPrepare event issue?)
What happened?
Description
Searching elements in our PROD environment CP is really slow, taking 8.7s NB - there's no slowness navigating around the CP, or in the API endpoints it delivers to the front-end app.
We've got 4+ environments for the site - PROD, UAT, DEV (all aws Ubuntu. uat/dev lower spec than prod) and local (ddev). We constantly pull the PROD database back to UAT/DEV/LOCAL for testing and releases etc.
In all the other environments with the same DB loaded search times are 140ms.
Looking at the request in the debugger I can see there's a 8s jump in the fired events when the first afterPrepare is fired.
I've run MySQL CHECK TABLE on all the tables I think might need it:
- elements
- searchindex And these all report OK.
Any ideas?
Steps to reproduce
Expected behavior
Actual behavior
Craft CMS version
Craft Pro 4.4.16
PHP version
8.1.14
Operating system and version
Linux 5.4.0-1018-aws
Database type and version
MySQL 8.0.31
Image driver and version
No response
Installed plugins and versions
As a starting point, make sure your searchindex table is using the InnoDB storage engine (and not MyISAM) and run https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html on each of your tables to rebuild the table statistics an indexes and see if that helps.
