Answers.Body: <p>I ended up submitting a pull request that targets the mysql db to insert index hints in the relevant query.</p>
  <p><a href="https://github.com/craftcms/cms/pull/11825" rel="nofollow noreferrer">https://github.com/craftcms/cms/pull/11825</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-08-25T13:39:25.407Z
Answers.LastActivityDate: 2022-08-25T13:39:25.407Z
Answers.UserDisplayName: SeanJ
Body: <p>We're experiencing  performance issues in CP stemming from having a decent number (&gt;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:</p>
  <p><a href="https://i.sstatic.net/6iiKw.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/6iiKw.png" alt="Recent Entries query time" /></a></p>
  <p>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:
  <a href="https://github.com/craftcms/cms/blob/f09bf02b350f3eed5564c7e9240ae0cfeef31d41/src/elements/db/ElementQuery.php#L2318" rel="nofollow noreferrer">https://github.com/craftcms/cms/blob/f09bf02b350f3eed5564c7e9240ae0cfeef31d41/src/elements/db/ElementQuery.php#L2318</a></p>
  <p>Is there another way I can apply index hints without needing to go into core cms code? I would make a PR (like <a href="https://github.com/UFHealth/craftcms/pull/1/files#diff-f94272123a3a550345c7f794061fd3664d8cff428758179dad3cfe3cc89eaef2" rel="nofollow noreferrer">https://github.com/UFHealth/craftcms/pull/1/files#diff-f94272123a3a550345c7f794061fd3664d8cff428758179dad3cfe3cc89eaef2</a>) but postgres doesn't support index hints, so I assume folks at Pixel &amp; Tonic prob don't want to roll in db changes like this that can't be abstracted to support different databases.</p>
  <p>Our last resort would be to remove the dateDeleted index on structures but that seems too destructive, and prob will have performance ramifications elsewhere.</p>
  
Tags: <control-panel><database><performance><craft4>
Title: Using index hints to get around performance issues with large structures table
ThreadText: # Using index hints to get around performance issues with large structures table
  
  (Asked by: username_0 on 2022-08-23)
  
  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:
  
  [![Recent Entries query time](https://i.sstatic.net/6iiKw.png)](https://i.sstatic.net/6iiKw.png)
  
  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.
  
  **Tags:** control-panel, database, performance, craft4
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2022-08-25. Score: 1
  
  I ended up submitting a pull request that targets the mysql db to insert index hints in the relevant query.
  
  <https://github.com/craftcms/cms/pull/11825>
  
  
  ---
  
  