0

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

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?

  • 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 Nov 15 '19 at 22:11
  • 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 Nov 27 '19 at 15:22
  • 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 Nov 28 '19 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.