craftcms
[5.x]: Slow queries when pagination enabled especially COUNT(*) query
What happened?
Description
I've inherited an in progress migration to Craft, and we're hitting some slow "large amount of entries" related issues. Most notably is our search page, which when no filters added, is targeting 500K entries.
As we add filtering (and related Craft queryParams) it does get faster, but my first concern is the COUNT(*) query that is part of the paginate tag. I can add some caching, but
Steps to reproduce
- Add the
paginatetag to my search results template. Don't even add pagination output, just the tagpaginate. - Refresh the page
- Wait....
- Review slow queries in Yii Debugger.
Slowest Query:
SELECT COUNT(*)
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
WHERE (`entries`.`sectionId`=4) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2025-04-08 15:59:59') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2025-04-08 15:59:59'))) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
MySQL EXPLAIN ANALYZE of the above query:
-> Aggregate: count(0) (cost=340365 rows=1) (actual time=18456..18456 rows=1 loops=1)
-> Nested loop inner join (cost=339561 rows=8043) (actual time=23.1..18422 rows=507631 loops=1)
-> Nested loop inner join (cost=325263 rows=16086) (actual time=22.9..6471 rows=507631 loops=1)
-> Filter: ((entries.postDate <= TIMESTAMP'2025-04-08 15:59:59') and ((entries.expiryDate is null) or (entries.expiryDate > TIMESTAMP'2025-04-08 15:59:59'))) (cost=48433 rows=321722) (actual time=22.8..2120 rows=1.03e+6 loops=1)
-> Index lookup on entries using idx_yrrpauqjptnofrepewluqwnbilupfovljkxz (sectionId=4) (cost=48433 rows=1.29e+6) (actual time=22.8..2020 rows=1.03e+6 loops=1)
-> Filter: ((elements.archived = false) and (elements.enabled = true) and (elements.dateDeleted is null) and (elements.draftId is null) and (elements.revisionId is null)) (cost=0.76 rows=0.05) (actual time=0.0041..0.00413 rows=0.493 loops=1.03e+6)
-> Single-row index lookup on elements using PRIMARY (id=entries.id) (cost=0.76 rows=1) (actual time=0.00392..0.00395 rows=1 loops=1.03e+6)
-> Filter: (elements_sites.enabled = true) (cost=0.789 rows=0.5) (actual time=0.0231..0.0234 rows=1 loops=507631)
-> Index lookup on elements_sites using idx_daumghcxjtibkdpmzhahkolvwbgldtyjpfgu (elementId=entries.id) (cost=0.789 rows=1) (actual time=0.0229..0.0232 rows=1 loops=507631)
Query Params Object and related Twig
array:2 [▼
"section" => "films,tv"
"limit" => 20
]
{% set results = craft.entries(data.searchQueryParams) %}
{% paginate results as pageInfo, searchResults %}
Expected behavior
While I don't expect dealing with extremely large numbers of entries to be fast, I expect it to be a bit faster than 10+s COUNT(*) queries locally, and even slower on our web host.
Actual behavior
As mentioned, this large data set is resulting in very slow queries when enabling pagination. I'm open to any suggestions, including additional indexes, or rendering pagination other ways.
Craft CMS version
5.6.15
PHP version
8.2.27
Operating system and version
Linux 6.10.14-linuxkit
Database type and version
MySQL 8.0.40
Image driver and version
Imagick 3.7.0
Installed plugins and versions
Name Handle Package Name Version Installed Enabled
------------------------ ------------------- --------------------------------------- ------- --------- -------
Amazon S3 aws-s3 craftcms/aws-s3 2.2.1 Yes Yes
Button Box buttonbox verbb/buttonbox 5.0.0 Yes Yes
CKEditor ckeditor craftcms/ckeditor 4.6.0 Yes Yes
Control Panel CSS cp-css doublesecretagency/craft-cpcss 3.0.0 Yes Yes
Conventions conventions zaengle/craft-conventions 5.0.5 Yes Yes
CP Field Inspect cp-field-inspect mmikkel/cp-field-inspect 2.0.4 Yes Yes
Element API element-api craftcms/element-api 4.2.0 Yes Yes
Elements Panel elements-panel putyourlightson/craft-elements-panel 3.0.1 Yes Yes
Feed Me feed-me craftcms/feed-me 6.8.0 Yes Yes
Formie formie verbb/formie 3.0.22 Yes Yes
Hyper hyper verbb/hyper 2.2.4 Yes Yes
Imager X imager-x spacecatninja/imager-x 5.1.3 Yes Yes
Incognito Field incognito-field mmikkel/incognito-field 2.0.1 Yes Yes
oEmbed oembed wrav/oembed 3.1.3 Yes Yes
Postmark postmark craftcms/postmark 3.1.0 Yes Yes
SEOmatic seomatic nystudio107/craft-seomatic 5.1.12 Yes Yes
Servd Assets and Helpers servd-asset-storage servd/craft-asset-storage 4.0.15 Yes Yes
Sprig sprig putyourlightson/craft-sprig 3.5.2 Yes Yes
Template Comments templatecomments nystudio107/craft-templatecomments 5.0.4 Yes Yes
Toolbelt toolbelt zaengle/craft-toolbelt 5.0.7 Yes Yes
Typogrify typogrify nystudio107/craft-typogrify 5.0.1 Yes Yes
Vite vite nystudio107/craft-vite 5.0.1 Yes Yes
Webhooks webhooks craftcms/webhooks 3.1.0 Yes Yes
AsyncQueue async-queue ostark/craft-async-queue 4.0.0 Yes No
Related Elements related-elements mindseeker-media/craft-related-elements 1.1.2 Yes No
We are facing the same problem.
Both the COUNT (*) and actual query are taking quite some time, regularly more than a few seconds.
Our search index is only 83K rows and ~26MB.
UPDATE:
After running ANALYZE TABLE and OPTIMIZE TABLE on the searchindex table it seemed to have reduced the queries down to ~0.03 seconds
@victorlap @ajp was going to suggest that
There is also the db/repair CLI command, which will do this for you against all database tables.
https://craftcms.com/docs/5.x/reference/cli.html#db-repair
Try that @ajp and see if it helps your situation.
Same here, we have a query which takes about on average 3s locally on an M1 Pro:
SELECT COUNT(*)
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`
WHERE (`entries`.`sectionId`=11) AND (`elements_sites`.`siteId`=1) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2025-08-29 10:15:59') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2025-08-29 10:15:59'))) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
@ajp, I'm curious if you've made any progress. Running db/repair did not do that much for us, nothing really noticeable.
@victorlap you mention the searchindex table, but as far as I can see, that has nothing to do with the original query? Am I missing something perhaps?
Edit: aah I think you use it while searching, my usecase is just plain pagination of a section :/
We're again seeing this problem. It looks like on our side the elements_sites table is not cleaned up properly. We have ~169M of elements, and the elements_sites table has grown to 22G. It's unclear which rows we can remove.
