Body: <p>Craft CMS 3.3.15</p>
  
  <p>PHP 7.1.3</p>
  
  <p>MySQL 5.5.42</p>
  
  <p>I have a structure with 360 entries.</p>
  
  <p>Right now in the CP it takes 3.5 - 10 seconds to load the first 50.
  And another 3.5 - 10 seconds for every additional 50 when scrolling.</p>
  
  <p>From what I can see in the yii profiler, there are a LOT of queries, and many of them takes around 100 - 150 ms.</p>
  
  <p>These queries are basically a COUNT(...) query, 30-50 of them per request, each taking 100+ ms.</p>
  
  <p>Example:</p>
  
  <pre><code>SELECT COUNT(*) 
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` 
  AS `elementsSitesId`, `content`.`id` AS `contentId` 
  FROM `craft_elements` `elements` INNER JOIN `craft_entries` `entries` 
  ON `entries`.`id` = `elements`.`id` INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id` 
  INNER JOIN `craft_content` `content` 
  ON (`content`.`elementId` = `elements`.`id`) 
  AND (`content`.`siteId` = `elements_sites`.`siteId`) 
  INNER JOIN `craft_structureelements` `structureelements` 
  ON (`structureelements`.`elementId` = `elements`.`id`) 
  AND (`structureelements`.`structureId`=1) 
  WHERE (`elements_sites`.`siteId`='1') 
  AND (`elements`.`archived`=FALSE) 
  AND (((`elements`.`enabled`=TRUE) 
  AND (`elements_sites`.`enabled`=TRUE)) 
  AND (`entries`.`postDate` &lt;= '2019-11-15 12:15:04') 
  AND ((`entries`.`expiryDate` IS NULL) 
  OR (`entries`.`expiryDate` &gt; '2019-11-15 12:15:04'))) 
  AND (`elements`.`dateDeleted` IS NULL) 
  AND (`elements_sites`.`enabled`=TRUE) 
  AND ((`structureelements`.`lft` &gt; '537') 
  AND (`structureelements`.`rgt` &lt; '538') 
  AND (`structureelements`.`root`='1')) 
  AND (`elements`.`draftId` IS NULL) 
  AND (`elements`.`revisionId` IS NULL)) `subquery` 
  INNER JOIN `craft_entries` `entries` 
  ON `entries`.`id` = `subquery`.`elementsId` 
  INNER JOIN `craft_elements` `elements` 
  ON `elements`.`id` = `subquery`.`elementsId` 
  INNER JOIN `craft_elements_sites` `elements_sites` 
  ON `elements_sites`.`id` = `subquery`.`elementsSitesId` 
  INNER JOIN `craft_content` `content` 
  ON `content`.`id` = `subquery`.`contentId` 
  INNER JOIN `craft_structureelements` `structureelements` 
  ON (`structureelements`.`elementId` = `subquery`.`elementsId`) 
  AND (`structureelements`.`structureId`=1)
  </code></pre>
  
  <p>As you can see from the profiler, there is a lot of them going off:</p>
  
  <p><a href="https://i.sstatic.net/qqPpb.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/qqPpb.png" alt="enter image description here"></a></p>
  
  <p>I dont know if it has something to do with this beeing a structure, but the other channels are fast and problem free.</p>
  
  <p>Right now the client waits for 10-20 seconds each time they edit or add entries to the structure, and that is understandable frustrating.</p>
  
  <p>Is there any kind of index I can set to speed this up?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-11-27T15:22:12.813Z
Comments.Text: Yes it is, a pretty beefy one our company uses for some of its sites.
  I get the same timeline locally, but a bit faster, about 80-100ms instead of 150.
Comments.UserDisplayName: Syversen
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-11-28T14:42:40.837Z
Comments.Text: If I swap to ordering by title instead of structure, each page of 50 takes 100ms to load, instead of 4000-8000ms per "scroll".
Comments.UserDisplayName: Syversen
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-07-14T20:40:00.487Z
Comments.Text: Craft 3.4.27 has an [improvement](https://github.com/craftcms/cms/blob/68d7e40efc7630fc319a27e0536426028d7baa50/CHANGELOG-v3.md#changed).
  
  - Improved the performance of structured element index views.
  
  I've tested it on a client site and their Structures are loading much quicker now. 
Comments.UserDisplayName: Alex Aguilar
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-11-15T22:11:17.473Z
Comments.Text: 150ms is quite a long time for that query to be able to execute. Is your database in a shared hosting environment by chance?
Comments.UserDisplayName: Brad Bell
Tags: <craft3><control-panel><performance><mysql>
Title: Control panel - Entry indexes very slow
ThreadText: # Control panel - Entry indexes very slow
  
  (Asked by: username_0 on 2019-11-15)
  
  Craft CMS 3.3.15
  
  PHP 7.1.3
  
  MySQL 5.5.42
  
  I have a structure with 360 entries.
  
  Right now in the CP it takes 3.5 - 10 seconds to load the first 50.
  And another 3.5 - 10 seconds for every additional 50 when scrolling.
  
  From what I can see in the yii profiler, there are a LOT of queries, and many of them takes around 100 - 150 ms.
  
  These queries are basically a COUNT(...) query, 30-50 of them per request, each taking 100+ ms.
  
  Example:
  
  ```
  SELECT COUNT(*) 
  FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` 
  AS `elementsSitesId`, `content`.`id` AS `contentId` 
  FROM `craft_elements` `elements` INNER JOIN `craft_entries` `entries` 
  ON `entries`.`id` = `elements`.`id` INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id` 
  INNER JOIN `craft_content` `content` 
  ON (`content`.`elementId` = `elements`.`id`) 
  AND (`content`.`siteId` = `elements_sites`.`siteId`) 
  INNER JOIN `craft_structureelements` `structureelements` 
  ON (`structureelements`.`elementId` = `elements`.`id`) 
  AND (`structureelements`.`structureId`=1) 
  WHERE (`elements_sites`.`siteId`='1') 
  AND (`elements`.`archived`=FALSE) 
  AND (((`elements`.`enabled`=TRUE) 
  AND (`elements_sites`.`enabled`=TRUE)) 
  AND (`entries`.`postDate` <= '2019-11-15 12:15:04') 
  AND ((`entries`.`expiryDate` IS NULL) 
  OR (`entries`.`expiryDate` > '2019-11-15 12:15:04'))) 
  AND (`elements`.`dateDeleted` IS NULL) 
  AND (`elements_sites`.`enabled`=TRUE) 
  AND ((`structureelements`.`lft` > '537') 
  AND (`structureelements`.`rgt` < '538') 
  AND (`structureelements`.`root`='1')) 
  AND (`elements`.`draftId` IS NULL) 
  AND (`elements`.`revisionId` IS NULL)) `subquery` 
  INNER JOIN `craft_entries` `entries` 
  ON `entries`.`id` = `subquery`.`elementsId` 
  INNER JOIN `craft_elements` `elements` 
  ON `elements`.`id` = `subquery`.`elementsId` 
  INNER JOIN `craft_elements_sites` `elements_sites` 
  ON `elements_sites`.`id` = `subquery`.`elementsSitesId` 
  INNER JOIN `craft_content` `content` 
  ON `content`.`id` = `subquery`.`contentId` 
  INNER JOIN `craft_structureelements` `structureelements` 
  ON (`structureelements`.`elementId` = `subquery`.`elementsId`) 
  AND (`structureelements`.`structureId`=1)
  
  ```
  
  As you can see from the profiler, there is a lot of them going off:
  
  [![enter image description here](https://i.sstatic.net/qqPpb.png)](https://i.sstatic.net/qqPpb.png)
  
  I dont know if it has something to do with this beeing a structure, but the other channels are fast and problem free.
  
  Right now the client waits for 10-20 seconds each time they edit or add entries to the structure, and that is understandable frustrating.
  
  Is there any kind of index I can set to speed this up?
  
  **Tags:** craft3, control-panel, performance, mysql
  
  > **Comments:**
  > **username_2**: 150ms is quite a long time for that query to be able to execute. Is your database in a shared hosting environment by chance?  
  > **username_0**: Yes it is, a pretty beefy one our company uses for some of its sites. I get the same timeline locally, but a bit faster, about 80-100ms instead of 150.  
  > **username_0**: If I swap to ordering by title instead of structure, each page of 50 takes 100ms to load, instead of 4000-8000ms per "scroll".  
  > **username_1**: Craft 3.4.27 has an [improvement](https://github.com/craftcms/cms/blob/68d7e40efc7630fc319a27e0536426028d7baa50/CHANGELOG-v3.md#changed).  - Improved the performance of structured element index views.  I've tested it on a client site and their Structures are loading much quicker now.   
  
  ---
  
  ## 0 answers
  
  