Quick fix? Improve query performance on larger datasets

[lenvanessen]

We have:
8000 articles
25000 assets
8k users

And right now, we have slow performance on some simple queries, especially if we're trying to query against a field value.

For instance, finding an asset:
Twig code:
{% set entry = craft.assets.originaId(7199178).one() %}

Resulting query:

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `assets`.`uploaderId`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_alt_sxjohgdo`, `content`.`field_answers_tygamntk`, `content`.`field_body`, `content`.`field_completedRegistration_qchdvvhs`, `content`.`field_contentType_kyxwdomj`, `content`.`field_copyright_gfwfzhzb`, `content`.`field_country_iecvbgwi`, `content`.`field_darkMode_cxldzoux`, `content`.`field_description_swgutvjh`, `content`.`field_disabled_vzjyvmsv`, `content`.`field_embed_tgmajaqm`, `content`.`field_embedHtml_ymaynfgz`, `content`.`field_endDate_omlfmxgk`, `content`.`field_hidden_abkccahm`, `content`.`field_intro_nvfphrbd`, `content`.`field_rel_rqmyprus`, `content`.`field_location_rqxobnaa`, `content`.`field_newsletter_laxveajq`, `content`.`field_notifications_nnocfrbz`, `content`.`field_originalId_spvnsofp`, `content`.`field_entryDate_ryofrltd`, `content`.`field_raffles_alqbxcrw`, `content`.`field_seo_bdrmifmv`, `content`.`field_startDate_iruwhvkq`, `content`.`field_heading_fqcanuuv`, `content`.`field_topbarNotice_ieivkxiu`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `assets` `assets` ON `assets`.`id` = `elements`.`id`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`elements_sites`.`siteId`=1) AND (`content`.`field_originalId_spvnsofp`=7199178) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `elements`.`dateCreated` DESC
LIMIT 1) `subquery`
INNER JOIN `assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `elements`.`dateCreated` DESC

It takes well over 4 seconds on a 2016 macbook pro.

I'm not an SQL expert by any means, but i've been playing around for a bit trying to make some improvements

Since we're only trying to get assets in this query, i've made the following changes (see bold):

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `assets`.`uploaderId`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_alt_sxjohgdo`, `content`.`field_answers_tygamntk`, `content`.`field_body`, `content`.`field_completedRegistration_qchdvvhs`, `content`.`field_contentType_kyxwdomj`, `content`.`field_copyright_gfwfzhzb`, `content`.`field_country_iecvbgwi`, `content`.`field_darkMode_cxldzoux`, `content`.`field_description_swgutvjh`, `content`.`field_disabled_vzjyvmsv`, `content`.`field_embed_tgmajaqm`, `content`.`field_embedHtml_ymaynfgz`, `content`.`field_endDate_omlfmxgk`, `content`.`field_hidden_abkccahm`, `content`.`field_intro_nvfphrbd`, `content`.`field_rel_rqmyprus`, `content`.`field_location_rqxobnaa`, `content`.`field_newsletter_laxveajq`, `content`.`field_notifications_nnocfrbz`, `content`.`field_originalId_spvnsofp`, `content`.`field_entryDate_ryofrltd`, `content`.`field_raffles_alqbxcrw`, `content`.`field_seo_bdrmifmv`, `content`.`field_startDate_iruwhvkq`, `content`.`field_heading_fqcanuuv`, `content`.`field_topbarNotice_ieivkxiu`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `assets` `assets` ON `assets`.`id` = `elements`.`id`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`elements_sites`.`siteId`=1) AND (`content`.`field_originalId_spvnsofp`=7199178) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) AND (`elements`.`type` = 'craft\\elements\\Asset')
ORDER BY `elements`.`dateCreated` DESC
LIMIT 1) `subquery`
INNER JOIN `assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `elements`.`dateCreated` DESC

Total query time: 1.6s

So this already slices the time in half, can we possibly implement this if there are no adverse effects? Since we're running an asset query here, I don't see a problem with limiting elements that are joined by it's type.

But other than this, it seems to be a tad innefficient. If we'd looked in the content table first for this value and just got elements by their ids we'd be well under 50ms here

[brandonkelly]

First, try running php craft db/convert-charset utf8 utf8_unicode_ci. If your site was created before Craft 3.6.4, there’s a pretty good chance that will help (if you haven’t run it already).

How is your query performance affected if you add the type condition at the beginning of that WHERE clause?

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `assets`.`uploaderId`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_alt_sxjohgdo`, `content`.`field_answers_tygamntk`, `content`.`field_body`, `content`.`field_completedRegistration_qchdvvhs`, `content`.`field_contentType_kyxwdomj`, `content`.`field_copyright_gfwfzhzb`, `content`.`field_country_iecvbgwi`, `content`.`field_darkMode_cxldzoux`, `content`.`field_description_swgutvjh`, `content`.`field_disabled_vzjyvmsv`, `content`.`field_embed_tgmajaqm`, `content`.`field_embedHtml_ymaynfgz`, `content`.`field_endDate_omlfmxgk`, `content`.`field_hidden_abkccahm`, `content`.`field_intro_nvfphrbd`, `content`.`field_rel_rqmyprus`, `content`.`field_location_rqxobnaa`, `content`.`field_newsletter_laxveajq`, `content`.`field_notifications_nnocfrbz`, `content`.`field_originalId_spvnsofp`, `content`.`field_entryDate_ryofrltd`, `content`.`field_raffles_alqbxcrw`, `content`.`field_seo_bdrmifmv`, `content`.`field_startDate_iruwhvkq`, `content`.`field_heading_fqcanuuv`, `content`.`field_topbarNotice_ieivkxiu`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `assets` `assets` ON `assets`.`id` = `elements`.`id`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`elements`.`type` = 'craft\\elements\\Asset') AND (`elements_sites`.`siteId`=1) AND (`content`.`field_originalId_spvnsofp`=7199178) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `elements`.`dateCreated` DESC
LIMIT 1) `subquery`
INNER JOIN `assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `elements`.`dateCreated` DESC

If we'd looked in the content table first for this value and just got elements by their ids we'd be well under 50ms here

I’m not sure I’m following you here. As in it would be faster if we joined the content table before the others? And in which query—the outer one or the subquery?

[lenvanessen]

@lenvanessen Did you have a chance to test this?
2.1s

[brandonkelly]

So roughly equivalent to it being at the end of the WHERE clause, right?

[lenvanessen]

So roughly equivalent to it being at the end of the WHERE clause, right?

Yes, very close

[angrybrad]

@lenvanessen I've just reverted this for the next release (509962a) because it has some side effects of causing much worse performance in some scenarios. If you want to send a database dump and composer.json/lock files over to [email protected], we can try to find a fix that works more broadly.

[lenvanessen]

@angrybrad i’ll send it over tomorrow!

[lenvanessen]

@brandonkelly
Thanks for the swift response. The db-charset is already utf8_unicode_ci, it's a new db that has the articles imported into it.
Currently, I've done some more testing and added noticed that my column was a text-column, and not a int. It also did not have an index, index to the field_originalId_spvnsofp and that has drastically changed the ballgame here.

Your query, with index and column type int

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `assets`.`uploaderId`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_alt_sxjohgdo`, `content`.`field_answers_tygamntk`, `content`.`field_body`, `content`.`field_completedRegistration_qchdvvhs`, `content`.`field_contentType_kyxwdomj`, `content`.`field_copyright_gfwfzhzb`, `content`.`field_country_iecvbgwi`, `content`.`field_darkMode_cxldzoux`, `content`.`field_description_swgutvjh`, `content`.`field_disabled_vzjyvmsv`, `content`.`field_embed_tgmajaqm`, `content`.`field_embedHtml_ymaynfgz`, `content`.`field_endDate_omlfmxgk`, `content`.`field_hidden_abkccahm`, `content`.`field_intro_nvfphrbd`, `content`.`field_rel_rqmyprus`, `content`.`field_location_rqxobnaa`, `content`.`field_newsletter_laxveajq`, `content`.`field_notifications_nnocfrbz`, `content`.`field_originalId_spvnsofp`, `content`.`field_entryDate_ryofrltd`, `content`.`field_raffles_alqbxcrw`, `content`.`field_seo_bdrmifmv`, `content`.`field_startDate_iruwhvkq`, `content`.`field_heading_fqcanuuv`, `content`.`field_topbarNotice_ieivkxiu`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `assets` `assets` ON `assets`.`id` = `elements`.`id`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`elements`.`type` = 'craft\\elements\\Asset') AND (`elements_sites`.`siteId`=1) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `elements`.`dateCreated` DESC
LIMIT 1) `subquery`
INNER JOIN `assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `elements`.`dateCreated` DESC

Time: 14 ms

The same query with no index and column-type reverted to text

Time: 2.6 seconds

The original query with no index, column type text and no WHERE (elements.type = 'craft\\elements\\Asset')

Time: 4s

It seems that adding the type filter does quite improve the performance when searching in regular text fields or varchars.

" If we'd looked in the content table first for this value and just got elements by their ids we'd be well under 50ms here" > Never mind, was not using my brain here.

[brandonkelly]

Currently, I've done some more testing and added noticed that my column was a text-column, and not a int.

The column type is determined by the field type, and I would not recommend manually changing it – it will just end up getting changed back to TEXT the next time you save your field. If you want the column to be an INT, use a Number field instead of Plain Text.

It also did not have an index, index to the field_originalId_spvnsofp and that has drastically changed the ballgame here.

Yeah, adding indexes to the content table can be helpful but Craft can’t predict which ones you’re going to need (and it would be overkill to add indexes to every field column). It’s fine if you add it manually though.

[lenvanessen]

The column type is determined by the field type, and I would not recommend manually changing it – it will just end up getting changed back to TEXT the next time you save your field. If you want the column to be an INT, use a Number field instead of Plain Text.

Yes, already did change it in the CMS to a number field.

[lenvanessen]

@brandonkelly to give another example.

I'm trying to get the latest asset here, in twig:
{% set asset = craft.assets.orderBy('dateCreated', 'desc').limit(1).one() %}

Resulting sql:

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`, `assets`.`volumeId`, `assets`.`folderId`, `assets`.`filename`, `assets`.`kind`, `assets`.`width`, `assets`.`height`, `assets`.`size`, `assets`.`focalPoint`, `assets`.`keptFile`, `assets`.`dateModified`, `volumeFolders`.`path` AS `folderPath`, `assets`.`uploaderId`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_alt_sxjohgdo`, `content`.`field_answers_tygamntk`, `content`.`field_body`, `content`.`field_completedRegistration_qchdvvhs`, `content`.`field_contentType_kyxwdomj`, `content`.`field_copyright_gfwfzhzb`, `content`.`field_country_iecvbgwi`, `content`.`field_darkMode_cxldzoux`, `content`.`field_description_swgutvjh`, `content`.`field_disabled_vzjyvmsv`, `content`.`field_embed_tgmajaqm`, `content`.`field_embedHtml_ymaynfgz`, `content`.`field_endDate_omlfmxgk`, `content`.`field_hidden_abkccahm`, `content`.`field_intro_nvfphrbd`, `content`.`field_rel_rqmyprus`, `content`.`field_location_rqxobnaa`, `content`.`field_newsletter_laxveajq`, `content`.`field_notifications_nnocfrbz`, `content`.`field_originalId_spvnsofp`, `content`.`field_entryDate_ryofrltd`, `content`.`field_quotes_mxwtxnne`, `content`.`field_raffles_alqbxcrw`, `content`.`field_seo_bdrmifmv`, `content`.`field_startDate_iruwhvkq`, `content`.`field_heading_fqcanuuv`, `content`.`field_topbarNotice_ieivkxiu`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `assets` `assets` ON `assets`.`id` = `elements`.`id`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON (`content`.`elementId` = `elements`.`id`) AND (`content`.`siteId` = `elements_sites`.`siteId`)
WHERE (`elements_sites`.`siteId`=1) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `elements`.`dateCreated`
LIMIT 1) `subquery`
INNER JOIN `assets` `assets` ON `assets`.`id` = `subquery`.`elementsId`
INNER JOIN `volumefolders` `volumeFolders` ON `volumeFolders`.`id` = `assets`.`folderId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `elements`.`dateCreated`

It takes 3.7 seconds just to fetch this one row from the database. It seems that it's merging all rows, and joining all tables, and then after doing so it plucks the latest row.

`

[lenvanessen]

For reference, our the tables sizes:
assets: 27.408
elements: 73.482
elements_sites: 221.722
volumefolders: 11
content: 212.877

[brandonkelly]

Just released Craft 3.7.12 with the added elements.type condition. Thanks for the suggestion!

[brandonkelly]

We actually had to revert this, as it was having an adverse effect on performance for several sites.

[martinleveille]

Hi @brandonkelly with the 3.7.12 release, we have a little problem on our end related to this discussion.

For one of our site, we have created a custom element type that extend the \craft\elements\Entry and \craft\elements\db\EntryQuery. We have done this before we knew about the existence of behaviors. So maybe if we redo the same thing today, we will have a different approach.

Anyway, we use this new ExampleElement and ExampleQuery to have custom element index page, custom validation rules/scenarios and alter the query with beforePrepare and afterPrepare methods.

With the new ->andWhere(['elements.type' => $this->elementType]) condition added with this commit 636f778 , we try to find the best way to tell Craft that our ExampleElement is in fact an EntryElement because as you can see, all our queries returns no elements because the WHERE condition search for a \example\elements\ExampleElement in the type column.

I have try to put $this->elementType = Entry::class in our beforePrepare method, but I'm not sure if there another or a better way of doing this.

Thanks for your help :)

[brandonkelly]

Why not just always set elementType to craft\elements\Entry, rather than your ExampleElement class?

$query = new MyElementQueryClass(Entry::class);

[martinleveille]

Indeed! I did not think about passing that to the constructor. Thanks for the advice!