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?
