We're experiencing performance issues in CP stemming from having a decent number (>20k) of records in structures and the mysql is using the wrong index (dateDeleted instead of primary). This issue is affecting all entries/categories edit page as far as I can tell, just in different degrees. Even non-edit pages are affected as well. For example, the query for Recent Entries widget in the dashboard takes up to 240 seconds to finish:
This is actually fairly trivial to solve with mysql by using index hints (use index(primary)), but the problem is the query I need to fix is here: https://github.com/craftcms/cms/blob/f09bf02b350f3eed5564c7e9240ae0cfeef31d41/src/elements/db/ElementQuery.php#L2318
Is there another way I can apply index hints without needing to go into core cms code? I would make a PR (like https://github.com/UFHealth/craftcms/pull/1/files#diff-f94272123a3a550345c7f794061fd3664d8cff428758179dad3cfe3cc89eaef2) but postgres doesn't support index hints, so I assume folks at Pixel & Tonic prob don't want to roll in db changes like this that can't be abstracted to support different databases.
Our last resort would be to remove the dateDeleted index on structures but that seems too destructive, and prob will have performance ramifications elsewhere.
