[3.x]: Multiple duplicate queries for multi-site installs

[gixxy22]

What happened?

When multiple sites are used, particularly with feedme (but possibly on standard save behaviour) the same queries are used for EACH site when saving an entry. If an entry is saved to 20 sites, the amount of duplicate queries is huge, pointlessly making the same query over and over again.

In MySQL 8, theres no query cache so performance is not so good. Where there is a query_cache, for example in MariaDB, the performance is increased astronomically.

Craft CMS version

Craft Pro 3.7.40.1

PHP version

8.1

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[brandonkelly]

We just found and fixed a couple areas where we found a lot of duplicate queries in 3.7.40. Can you post examples of queries you’re seeing a lot of duplicates for?

[gixxy22]

sure, this is one of the most problematic, as its the one that takes the longest to execute and also duplicates for each site:

SELECT `elements_sites`.`siteId` FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`, `structureelements`.`structureId` 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`) LEFT JOIN `craft_structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (EXISTS (SELECT * FROM `craft_structures` WHERE (`id` = `structureelements`.`structureId`) AND (`dateDeleted` IS NULL))) LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId` WHERE (`elements_sites`.`siteId` IN (3, 4, 5, 6, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 2, 8, 9, 7, 10)) AND (`elements`.`id`=1012252) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`revisionId` IS NULL) ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC) `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` LEFT JOIN `craft_structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId` = `subquery`.`structureId`) LEFT JOIN `craft_drafts` `drafts` ON `drafts`.`id` = `elements`.`draftId` ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC

[brandonkelly]

That query should no longer show up as duplicated when saving entries in 3.7.42 and onward.

If you don’t mind, I’d appreciate if you could test it out, and see if it makes a noticeable improvement on your site.

To do that, change your craftcms/cms requirement in composer.json to "dev-release/3.7.42 as 3.7.41" and run composer update.

[gixxy22]

applied, but hard to tell if any improvements as ive since switched to mariadb with query caching. (which has had a massive increase in performance by itself)

[brandonkelly]

Going to close this then. Let me know if you come across any additional expensive duplicate queries.

[brandonkelly]

3.7.42 is out now with those improvements.