[4.x]: Slow CP entry/user searching 8 seconds+ (AfterPrepare event issue?)

[anchovy]

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

[angrybrad]

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.