<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>

CC BY-SA 4.0
2019-11-27T15:22:12.813Z
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.
Syversen
CC BY-SA 4.0
2019-11-28T14:42:40.837Z
If I swap to ordering by title instead of structure, each page of 50 takes 100ms to load, instead of 4000-8000ms per "scroll".
Syversen
CC BY-SA 4.0
2020-07-14T20:40:00.487Z
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. 
Alex Aguilar
CC BY-SA 4.0
2019-11-15T22:11:17.473Z
150ms is quite a long time for that query to be able to execute. Is your database in a shared hosting environment by chance?
Brad Bell
<craft3><control-panel><performance><mysql>
Control panel - Entry indexes very slow
# 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:

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

