search

nystudio107 / craft-seomatic

SEOmatic facilitates modern SEO best practices & implementation for Craft CMS 3. It is a turnkey SEO system that is comprehensive, powerful, and flexible.
https://nystudio107.com/plugins/seomatic
Other
166 stars 70 forks source link

Duplicated db queries #561

Closed pieterjandebruyne closed 4 years ago

pieterjandebruyne commented 4 years ago

Hi,

I activated Seomatic on my e-commerce craft 3 website.

The following queries are from a commerce product detail page:

If I use the default image for facebook sharing, it gives 9 duplicated queries trying to fetch that image:

SELECT `draftId`, `revisionId`
FROM `craft_elements`
WHERE `id`=55916
LIMIT 1

x9

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`enabled` AS `enabledForSite`, `elements_sites`.`uri`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_aboutText`, `content`.`field_lastName`, `content`.`field_address`, `content`.`field_altText`, `content`.`field_apb`, `content`.`field_pharmacistPrice`, `content`.`field_apothekerTitularis`, `content`.`field_atc`, `content`.`field_filename`, `content`.`field_leaflets`, `content`.`field_sideEffects`, `content`.`field_btw`, `content`.`field_cnk`, `content`.`field_composition`, `content`.`field_contactInfo`, `content`.`field_contraIndication`, `content`.`field_createdAt`, `content`.`field_ctiExtended`, `content`.`field_doctor`, `content`.`field_emailAdres`, `content`.`field_ean`, `content`.`field_embedUrl`, `content`.`field_faggUrl`, `content`.`field_featureLabel`, `content`.`field_photoAuthorIsLogo`, `content`.`field_gdpr`, `content`.`field_birthday`, `content`.`field_usage`, `content`.`field_gemeente`, `content`.`field_sex`, `content`.`field_headerIntroText`, `content`.`field_headerBtnText`, `content`.`field_headerTitle`, `content`.`field_packageQuantity`, `content`.`field_importHash`, `content`.`field_importId`, `content`.`field_importOrder`, `content`.`field_indication`, `content`.`field_infoBarText`, `content`.`field_infoboxBullets`, `content`.`field_infoboxTitle`, `content`.`field_introText`, `content`.`field_clientAppointment`, `content`.`field_cookieConsentButtonLabel`, `content`.`field_btnTextDetailPage`, `content`.`field_shortDescription`, `content`.`field_fullDescription`, `content`.`field_supplierReference`, `content`.`field_medicinerollStep1`, `content`.`field_medicinerollStep2`, `content`.`field_medicinerollStep3`, `content`.`field_isLogo`, `content`.`field_newsletter`, `content`.`field_nut`, `content`.`field_openingHours`, `content`.`field_noteMedicinerole`, `content`.`field_notePrescription`, `content`.`field_oldSiteId`, `content`.`field_prescription`, `content`.`field_expires`, `content`.`field_promotionDescription`, `content`.`field_promoLink`, `content`.`field_properties`, `content`.`field_quote`, `content`.`field_refundValueWithOmnio`, `content`.`field_refundValueWithoutOmnio`, `content`.`field_seoGeneral`, `content`.`field_subTitle`, `content`.`field_cookieConsentText`, `content`.`field_fullText`, `content`.`field_phone`, `content`.`field_heading`, `content`.`field_titleFooter`, `content`.`field_mobileTitle`, `content`.`field_showCookieConsent`, `content`.`field_showInSubnav`, `content`.`field_showLinkToDetailPage`, `content`.`field_udi`, `content`.`field_updatedAt`, `content`.`field_vat`, `content`.`field_firstName`

x9

Using an image from a field (In the general Content SEO settings) shows 2queries that are duplicated 18 times (in the craft admin debug bar)

The 2 queries are:

`SELECT EXISTS(SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`enabled` AS `enabledForSite`, `elements_sites`.`uri`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_aboutText`, `content`.`field_lastName`, `content`.`field_address`, `content`.`field_altText`, `content`.`field_apb`, `content`.`field_pharmacistPrice`, `content`.`field_apothekerTitularis`, `content`.`field_atc`, `content`.`field_filename`, `content`.`field_leaflets`, `content`.`field_sideEffects`, `content`.`field_btw`, `content`.`field_cnk`, `content`.`field_composition`, `content`.`field_contactInfo`, `content`.`field_contraIndication`, `content`.`field_createdAt`, `content`.`field_ctiExtended`, `content`.`field_doctor`, `content`.`field_emailAdres`, `content`.`field_ean`, `content`.`field_embedUrl`, `content`.`field_faggUrl`, `content`.`field_featureLabel`, `content`.`field_photoAuthorIsLogo`, `content`.`field_gdpr`, `content`.`field_birthday`, `content`.`field_usage`, `content`.`field_gemeente`, `content`.`field_sex`, `content`.`field_headerIntroText`, `content`.`field_headerBtnText`, `content`.`field_headerTitle`, `content`.`field_packageQuantity`, `content`.`field_importHash`, `content`.`field_importId`, `content`.`field_importOrder`, `content`.`field_indication`, `content`.`field_infoBarText`, `content`.`field_infoboxBullets`, `content`.`field_infoboxTitle`, `content`.`field_introText`, `content`.`field_clientAppointment`, `content`.`field_cookieConsentButtonLabel`, `content`.`field_btnTextDetailPage`, `content`.`field_shortDescription`, `content`.`field_fullDescription`, `content`.`field_supplierReference`, `content`.`field_medicinerollStep1`, `content`.`field_medicinerollStep2`, `content`.`field_medicinerollStep3`, `content`.`field_isLogo`, `content`.`field_newsletter`, `content`.`field_nut`, `content`.`field_openingHours`, `content`.`field_noteMedicinerole`, `content`.`field_notePrescription`, `content`.`field_oldSiteId`, `content`.`field_prescription`, `content`.`field_expires`, `content`.`field_promotionDescription`, `content`.`field_promoLink`, `content`.`field_properties`, `content`.`field_quote`, `content`.`field_refundValueWithOmnio`, `content`.`field_refundValueWithoutOmnio`, `content`.`field_seoGeneral`, `content`.`field_subTitle`, `content`.`field_cookieConsentText`, `content`.`field_fullText`, `content`.`field_phone`, `content`.`field_heading`, `content`.`field_titleFooter`, `content`.`field_mobileTitle`, `content`.`field_showCookieConsent`, `content`.`field_showInSubnav`, `content`.`field_showLinkToDetailPage`, `content`.`field_udi`, `content`.`field_updatedAt`, `content`.`field_vat`, `content`.`field_firstName`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_assets` `assets` ON `assets`.`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_relations` `relations` ON (`relations`.`targetId` = `elements`.`id`) AND ((`relations`.`sourceId`='29125') AND (`relations`.`fieldId`='133')) AND ((`relations`.`sourceSiteId` IS NULL) OR (`relations`.`sourceSiteId`='1'))
WHERE (`elements_sites`.`siteId`=1) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `relations`.`sortOrder`
LIMIT 1) `subquery`
INNER JOIN `craft_assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_volumefolders` `volumeFolders` ON `assets`.`folderId` = `volumeFolders`.`id`
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_relations` `relations` ON (`relations`.`targetId` = `elements`.`id`) AND ((`relations`.`sourceId`='29125') AND (`relations`.`fieldId`='133')) AND ((`relations`.`sourceSiteId` IS NULL) OR (`relations`.`sourceSiteId`='1'))
ORDER BY `relations`.`sortOrder`)`

x18

`SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`enabled` AS `enabledForSite`, `elements_sites`.`uri`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_aboutText`, `content`.`field_lastName`, `content`.`field_address`, `content`.`field_altText`, `content`.`field_apb`, `content`.`field_pharmacistPrice`, `content`.`field_apothekerTitularis`, `content`.`field_atc`, `content`.`field_filename`, `content`.`field_leaflets`, `content`.`field_sideEffects`, `content`.`field_btw`, `content`.`field_cnk`, `content`.`field_composition`, `content`.`field_contactInfo`, `content`.`field_contraIndication`, `content`.`field_createdAt`, `content`.`field_ctiExtended`, `content`.`field_doctor`, `content`.`field_emailAdres`, `content`.`field_ean`, `content`.`field_embedUrl`, `content`.`field_faggUrl`, `content`.`field_featureLabel`, `content`.`field_photoAuthorIsLogo`, `content`.`field_gdpr`, `content`.`field_birthday`, `content`.`field_usage`, `content`.`field_gemeente`, `content`.`field_sex`, `content`.`field_headerIntroText`, `content`.`field_headerBtnText`, `content`.`field_headerTitle`, `content`.`field_packageQuantity`, `content`.`field_importHash`, `content`.`field_importId`, `content`.`field_importOrder`, `content`.`field_indication`, `content`.`field_infoBarText`, `content`.`field_infoboxBullets`, `content`.`field_infoboxTitle`, `content`.`field_introText`, `content`.`field_clientAppointment`, `content`.`field_cookieConsentButtonLabel`, `content`.`field_btnTextDetailPage`, `content`.`field_shortDescription`, `content`.`field_fullDescription`, `content`.`field_supplierReference`, `content`.`field_medicinerollStep1`, `content`.`field_medicinerollStep2`, `content`.`field_medicinerollStep3`, `content`.`field_isLogo`, `content`.`field_newsletter`, `content`.`field_nut`, `content`.`field_openingHours`, `content`.`field_noteMedicinerole`, `content`.`field_notePrescription`, `content`.`field_oldSiteId`, `content`.`field_prescription`, `content`.`field_expires`, `content`.`field_promotionDescription`, `content`.`field_promoLink`, `content`.`field_properties`, `content`.`field_quote`, `content`.`field_refundValueWithOmnio`, `content`.`field_refundValueWithoutOmnio`, `content`.`field_seoGeneral`, `content`.`field_subTitle`, `content`.`field_cookieConsentText`, `content`.`field_fullText`, `content`.`field_phone`, `content`.`field_heading`, `content`.`field_titleFooter`, `content`.`field_mobileTitle`, `content`.`field_showCookieConsent`, `content`.`field_showInSubnav`, `content`.`field_showLinkToDetailPage`, `content`.`field_udi`, `content`.`field_updatedAt`, `content`.`field_vat`, `content`.`field_firstName`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_assets` `assets` ON `assets`.`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_relations` `relations` ON (`relations`.`targetId` = `elements`.`id`) AND ((`relations`.`sourceId`='29125') AND (`relations`.`fieldId`='133')) AND ((`relations`.`sourceSiteId` IS NULL) OR (`relations`.`sourceSiteId`='1'))
WHERE (`elements_sites`.`siteId`=1) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements_sites`.`enabled`=TRUE) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `relations`.`sortOrder`
LIMIT 1) `subquery`
INNER JOIN `craft_assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_volumefolders` `volumeFolders` ON `assets`.`folderId` = `volumeFolders`.`id`
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_relations` `relations` ON (`relations`.`targetId` = `elements`.`id`) AND ((`relations`.`sourceId`='29125') AND (`relations`.`fieldId`='133')) AND ((`relations`.`sourceSiteId` IS NULL) OR (`relations`.`sourceSiteId`='1'))
ORDER BY `relations`.`sortOrder``

x18

I can not pinpoint why these queries get executed 18 times, enabling seomatic makes the load time go from 500ms to 2500ms..

Could you guys explain why these queries get duplicated or is this a bug?

Thanks for having a look, Pieter-Jan

Craft CMS 3.3.20.1 SEOmatic 3.2.43

khalwat commented 4 years ago

So without greater context, I can't explain the duplicated queries, other than to say they don't seem to be coming from SEOmatic.

I can tell you about load times, and that in local dev, SEOmatic sets its caches to only 30 seconds, whereas in production, you'll get a performant page. Check out this article for details:

https://nystudio107.com/blog/tips-for-using-seomatic-effectively#caching-and-cache-busting

After reading the above, if you still have issues with SEOmatic's performance in production, I can re-open the issue.

But this is very likely something you're experiencing with devMode on or in a local dev environment.

john-henry commented 4 years ago

Hi, @khalwat I believe this is still a large issue. I can see it happening in all my most recent sites. Depending on the complexity the queries get larger and larger.

Locally I created vanilla install with just Craft and SEOmatic and went through some troubleshooting for you to see if this is an issue or not worth investigating. It's making it very hard at the moment to do any performance work as I can't get rid of these duplicate queries.

Here is a video with my walkthrough.

https://share.getcloudapp.com/rRul6rdr

Happy to bundle up the vanilla site for you if you want them

khalwat commented 4 years ago

@john-henry in local dev, the SEOmatic caches are only 30 seconds. Turn devMode off to see what it's like with caches in place

john-henry commented 4 years ago

Just noting that I had a test with devmode off in my video above.

Here is a second video with vanilla after adding a few items to SEOmatic itself like some images. Similar to @pieter-janDB above

https://share.getcloudapp.com/OAuBbqXP

I'm trying to find some time to show you some production sites but even with this vanilla install it seems like a lot of overhead to me before we even get to the caching layers.

MadMikeyB commented 3 years ago

Hi @khalwat I'm also seeing what @john-henry is seeing - I watched his video and yep, seems very familiar. Has this been addressed at all?

khalwat commented 3 years ago

@MadMikeyB I'm not aware of anything that needs to be address here?

Yii2 has a built in query cache, so if the same query is executed more than once on a request, there is very little overhead involved.

Just seeing that duplicate queries exist is not necessarily a problem. What you should be concerned with is the overload load time, not duplicate queries.

Local dev with its 30 second caches will be slower, due to the number of cache misses. SEOmatic is doing a lot -- if you wrote Twig code to pull from a variety of fields, assets, etc. you would end up with similar overhead. SEOmatic's two-level cache should make this a non-issue in production.

Everything in @john-henry 's video looks normal to me, I believe we hashed this out on Discord IIRC.

khalwat commented 3 years ago

To be clear: simply seeing duplicate queries is not necessarily a problem.