craftcms
[5.x]: Slow entry queries when entries > ~60k
What happened?
Description
We’re noticing very slow query performance (5+ seconds) even on simple entry queries such as craft.entries.section('foo').limit(1). The generated SQL is:
SELECT
`elements`.`id`,
`elements`.`canonicalId`,
`elements`.`fieldLayoutId`,
`elements`.`uid`,
`elements`.`enabled`,
`elements`.`archived`,
`elements`.`dateLastMerged`,
`elements`.`dateCreated`,
`elements`.`dateUpdated`,
`elements_sites`.`id` AS `siteSettingsId`,
`elements_sites`.`siteId`,
`elements_sites`.`title`,
`elements_sites`.`slug`,
`elements_sites`.`uri`,
`elements_sites`.`content`,
`elements_sites`.`enabled` AS `enabledForSite`,
`entries`.`sectionId`,
`entries`.`fieldId`,
`entries`.`primaryOwnerId`,
`entries`.`typeId`,
`entries`.`postDate`,
`entries`.`expiryDate`
FROM (
SELECT
`elements`.`id` AS `elementsId`,
`elements_sites`.`id` AS `siteSettingsId`
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`=9) AND
(
((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND
(`entries`.`postDate` <= '2025-06-27 06:24:59') AND
((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2025-06-27 06:24:59'))
) AND
(`elements`.`archived`=FALSE) AND
(`elements`.`dateDeleted` IS NULL) AND
(`elements`.`draftId` IS NULL) AND
(`elements`.`revisionId` IS NULL)
ORDER BY
`entries`.`postDate` DESC, `elements`.`id` DESC LIMIT 1
) `subquery`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`siteSettingsId`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
ORDER BY `entries`.`postDate` DESC, `elements`.`id` DESC
Here’s the EXPLAIN:
+----+-------------+----------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+-----------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+-----------------------+--------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | PRIMARY | elements | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | elements_sites | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | entries | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | entries | NULL | ref | PRIMARY,idx_ksmlmbvmmikloqsofiihfwsxegmgqsvezqxh,idx_ulddeumylsxzeggxdqzktsecaggzyaevqcnb,idx_qcbmppnyoflswgsqutjnyowcpwczdhbomyct | idx_qcbmppnyoflswgsqutjnyowcpwczdhbomyct | 5 | const | 165358 | 25.00 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | elements | NULL | eq_ref | PRIMARY,idx_edfjbksrrkbgkzaeknawfytoefdgnadkblqi,idx_xdqidlahjwqvfefhfbsilzgoknjazjrotefs,idx_qxjenlnejaitvweaejowinbbdqfbaqrzzzoj,idx_zyrgnjgnpubtsldmzbcgxlpnjsudwxukxtpm,idx_gyvxtrvyjqdxljtpvtvzhwlqpysvonaxdtlu,fk_fcztkclrauuiretkpfkuluhsdatgqhwhdcsg,fk_qmdusssaykcqsyhbikrjmvtgoirhdtqdhssf | PRIMARY | 4 | project_craft.entries.id | 1 | 5.00 | Using where |
| 2 | DERIVED | elements_sites | NULL | ref | idx_htwagouujprmwucwwcvheihbvsclgmorodud,idx_wlhjritkeadcrqqxflqdsvryrsryoevhqolo | idx_htwagouujprmwucwwcvheihbvsclgmorodud | 4 | project_craft.entries.id | 1 | 50.00 | Using where |
+----+-------------+----------------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------+---------+-----------------------+--------+----------+----------------------------------------------+
Running the same query on a local server with MariaDB / Craft 5.7.11 and ~40k entries, the times are slightly better (0.4s to 0.7s) but still far higher than expected.
Steps to reproduce
- Seed around 40k to 60k entries on a Craft install
- Run
craft.entries.section('foo').one()
Craft CMS version
5.7.7
PHP version
8.2.28
Operating system and version
Linux 6.8.0-51-generic
Database type and version
MySQL 8.4.5
Image driver and version
No response
Installed plugins and versions
@rungta as a first step, can you try running https://craftcms.com/docs/5.x/reference/cli.html#db-repair against the database and see if the behavior changes?
@angrybrad The query time has reduced from 5s to around 2-4s after running ./craft db/repair. Still seems excessive though.
@rungta can you verify that database charsets and collations are consistent across all of the tables and columns?
You can use php craft db/convert-charset to help ensure.
Can you also run the SQL select version(); and share the output on the slow site? I want to make sure we’re not dealing with MariaDB masquerading as MySQL.
It is definitely MySQL. I had myself re-created this entire server and to move away from MariaDB to MySQL a couple months back. Nevertheless, here is select version(); and DB collations as requested:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.5 |
+-----------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
mysql> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='project_craft';
+----------------------------+--------------------+
| TABLE_NAME | TABLE_COLLATION |
+----------------------------+--------------------+
| addresses | utf8mb4_unicode_ci |
| announcements | utf8mb4_unicode_ci |
| assetindexdata | utf8mb4_unicode_ci |
| assetindexingsessions | utf8mb4_unicode_ci |
| assets | utf8mb4_unicode_ci |
| assets_sites | utf8mb4_unicode_ci |
| auth_oauth_tokens | utf8mb4_unicode_ci |
| authenticator | utf8mb4_unicode_ci |
| bulkopevents | utf8mb4_0900_ai_ci |
| categories | utf8mb4_unicode_ci |
| categorygroups | utf8mb4_unicode_ci |
| categorygroups_sites | utf8mb4_unicode_ci |
| changedattributes | utf8mb4_unicode_ci |
| changedfields | utf8mb4_unicode_ci |
| consume_clients | utf8mb4_unicode_ci |
| craftidtokens | utf8mb4_unicode_ci |
| deprecationerrors | utf8mb4_unicode_ci |
| drafts | utf8mb4_unicode_ci |
| elementactivity | utf8mb4_unicode_ci |
| elements | utf8mb4_unicode_ci |
| elements_bulkops | utf8mb4_unicode_ci |
| elements_owners | utf8mb4_unicode_ci |
| elements_sites | utf8mb4_unicode_ci |
| entries | utf8mb4_unicode_ci |
| entries_authors | utf8mb4_unicode_ci |
| entrytypes | utf8mb4_unicode_ci |
| feedme_feeds | utf8mb4_unicode_ci |
| feedme_logs | utf8mb4_unicode_ci |
| fieldlayouts | utf8mb4_unicode_ci |
| fields | utf8mb4_unicode_ci |
| globalsets | utf8mb4_unicode_ci |
| gqlschemas | utf8mb4_unicode_ci |
| gqltokens | utf8mb4_unicode_ci |
| imagetransformindex | utf8mb4_unicode_ci |
| imagetransforms | utf8mb4_unicode_ci |
| info | utf8mb4_unicode_ci |
| migrations | utf8mb4_unicode_ci |
| navigation_navs | utf8mb4_unicode_ci |
| navigation_navs_sites | utf8mb4_unicode_ci |
| navigation_nodes | utf8mb4_unicode_ci |
| plugins | utf8mb4_unicode_ci |
| projectconfig | utf8mb4_unicode_ci |
| queue | utf8mb4_unicode_ci |
| recoverycodes | utf8mb4_unicode_ci |
| relations | utf8mb4_unicode_ci |
| resourcepaths | utf8mb4_unicode_ci |
| revisions | utf8mb4_unicode_ci |
| searchindex | utf8mb4_unicode_ci |
| searchindexqueue | utf8mb4_0900_ai_ci |
| searchindexqueue_fields | utf8mb4_0900_ai_ci |
| sections | utf8mb4_unicode_ci |
| sections_entrytypes | utf8mb4_unicode_ci |
| sections_sites | utf8mb4_unicode_ci |
| sequences | utf8mb4_unicode_ci |
| sessions | utf8mb4_unicode_ci |
| shunnedmessages | utf8mb4_unicode_ci |
| sitegroups | utf8mb4_unicode_ci |
| sites | utf8mb4_unicode_ci |
| sso_identities | utf8mb4_unicode_ci |
| structureelements | utf8mb4_unicode_ci |
| structures | utf8mb4_unicode_ci |
| systemmessages | utf8mb4_unicode_ci |
| taggroups | utf8mb4_unicode_ci |
| tags | utf8mb4_unicode_ci |
| tokens | utf8mb4_unicode_ci |
| usergroups | utf8mb4_unicode_ci |
| usergroups_users | utf8mb4_unicode_ci |
| userpermissions | utf8mb4_unicode_ci |
| userpermissions_usergroups | utf8mb4_unicode_ci |
| userpermissions_users | utf8mb4_unicode_ci |
| userpreferences | utf8mb4_unicode_ci |
| users | utf8mb4_unicode_ci |
| volumefolders | utf8mb4_unicode_ci |
| volumes | utf8mb4_unicode_ci |
| webauthn | utf8mb4_unicode_ci |
| widgets | utf8mb4_unicode_ci |
| workflow_reviews | utf8mb4_unicode_ci |
| workflow_submissions | utf8mb4_unicode_ci |
+----------------------------+--------------------+
78 rows in set (0.00 sec)
Ran the db/convert-charset command, accepted the defaults:
$ ./craft db/convert-charset
Which character set should be used? [utf8mb4]
Which collation should be used? [utf8mb4_0900_ai_ci]
# truncated
Finished converting tables to utf8mb4/utf8mb4_0900_ai_ci.
After that we get:
mysql> SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='nswa_craft';
+----------------------------+--------------------+
| TABLE_NAME | TABLE_COLLATION |
+----------------------------+--------------------+
| addresses | utf8mb4_0900_ai_ci |
| announcements | utf8mb4_0900_ai_ci |
| assetindexdata | utf8mb4_0900_ai_ci |
| assetindexingsessions | utf8mb4_0900_ai_ci |
| assets | utf8mb4_0900_ai_ci |
| assets_sites | utf8mb4_0900_ai_ci |
| auth_oauth_tokens | utf8mb4_0900_ai_ci |
| authenticator | utf8mb4_0900_ai_ci |
| bulkopevents | utf8mb4_0900_ai_ci |
| categories | utf8mb4_0900_ai_ci |
| categorygroups | utf8mb4_0900_ai_ci |
| categorygroups_sites | utf8mb4_0900_ai_ci |
| changedattributes | utf8mb4_0900_ai_ci |
| changedfields | utf8mb4_0900_ai_ci |
| consume_clients | utf8mb4_0900_ai_ci |
| craftidtokens | utf8mb4_0900_ai_ci |
| deprecationerrors | utf8mb4_0900_ai_ci |
| drafts | utf8mb4_0900_ai_ci |
| elementactivity | utf8mb4_0900_ai_ci |
| elements | utf8mb4_0900_ai_ci |
| elements_bulkops | utf8mb4_0900_ai_ci |
| elements_owners | utf8mb4_0900_ai_ci |
| elements_sites | utf8mb4_0900_ai_ci |
| entries | utf8mb4_0900_ai_ci |
| entries_authors | utf8mb4_0900_ai_ci |
| entrytypes | utf8mb4_0900_ai_ci |
| feedme_feeds | utf8mb4_0900_ai_ci |
| feedme_logs | utf8mb4_0900_ai_ci |
| fieldlayouts | utf8mb4_0900_ai_ci |
| fields | utf8mb4_0900_ai_ci |
| globalsets | utf8mb4_0900_ai_ci |
| gqlschemas | utf8mb4_0900_ai_ci |
| gqltokens | utf8mb4_0900_ai_ci |
| imagetransformindex | utf8mb4_0900_ai_ci |
| imagetransforms | utf8mb4_0900_ai_ci |
| info | utf8mb4_0900_ai_ci |
| migrations | utf8mb4_0900_ai_ci |
| navigation_navs | utf8mb4_0900_ai_ci |
| navigation_navs_sites | utf8mb4_0900_ai_ci |
| navigation_nodes | utf8mb4_0900_ai_ci |
| plugins | utf8mb4_0900_ai_ci |
| projectconfig | utf8mb4_0900_ai_ci |
| queue | utf8mb4_0900_ai_ci |
| recoverycodes | utf8mb4_0900_ai_ci |
| relations | utf8mb4_0900_ai_ci |
| resourcepaths | utf8mb4_0900_ai_ci |
| revisions | utf8mb4_0900_ai_ci |
| searchindex | utf8mb4_0900_ai_ci |
| searchindexqueue | utf8mb4_0900_ai_ci |
| searchindexqueue_fields | utf8mb4_0900_ai_ci |
| sections | utf8mb4_0900_ai_ci |
| sections_entrytypes | utf8mb4_0900_ai_ci |
| sections_sites | utf8mb4_0900_ai_ci |
| sequences | utf8mb4_0900_ai_ci |
| sessions | utf8mb4_0900_ai_ci |
| shunnedmessages | utf8mb4_0900_ai_ci |
| sitegroups | utf8mb4_0900_ai_ci |
| sites | utf8mb4_0900_ai_ci |
| sso_identities | utf8mb4_0900_ai_ci |
| structureelements | utf8mb4_0900_ai_ci |
| structures | utf8mb4_0900_ai_ci |
| systemmessages | utf8mb4_0900_ai_ci |
| taggroups | utf8mb4_0900_ai_ci |
| tags | utf8mb4_0900_ai_ci |
| tokens | utf8mb4_0900_ai_ci |
| usergroups | utf8mb4_0900_ai_ci |
| usergroups_users | utf8mb4_0900_ai_ci |
| userpermissions | utf8mb4_0900_ai_ci |
| userpermissions_usergroups | utf8mb4_0900_ai_ci |
| userpermissions_users | utf8mb4_0900_ai_ci |
| userpreferences | utf8mb4_0900_ai_ci |
| users | utf8mb4_0900_ai_ci |
| volumefolders | utf8mb4_0900_ai_ci |
| volumes | utf8mb4_0900_ai_ci |
| webauthn | utf8mb4_0900_ai_ci |
| widgets | utf8mb4_0900_ai_ci |
| workflow_reviews | utf8mb4_0900_ai_ci |
| workflow_submissions | utf8mb4_0900_ai_ci |
+----------------------------+--------------------+
78 rows in set (0.00 sec)
mysql> SELECT `elements`.`id`, `elements`.`canonicalId`, ... # truncated
# truncated
1 row in set (2.31 sec)
# 2nd attempt
1 row in set (2.79 sec)
# 3rd attempt
1 row in set (3.05 sec)
@rungta welp, that’s all of the obvious stuff covered. Any chance we could get a database so we can try to reproduce locally with the SQL here? If so, could you send it to [email protected] and reference issue #17503?
We believed we were having a similar issue. We have around 120K entries for a particular site, and anytime we did any query that involved entries, it was unreasonably slow. Adjusting 'innodb_buffer_pool_size' from 128MB to a larger amount—in our case, 4GB—seemed to resolve this issue for us.
I hope this can get some attention. I am more than happy to provide a DB and project config, but in our tests, it's considerably slower indeed with big amount of entries.
We believed we were having a similar issue. We have around 120K entries for a particular site, and anytime we did any query that involved entries, it was unreasonably slow. Adjusting 'innodb_buffer_pool_size' from 128MB to a larger amount—in our case, 4GB—seemed to resolve this issue for us.
@JacobTrapp02 how did you end up with the 4GB? We also have it at 4GB currently, but still.... slow. Just increase it until you're happy? Mind sharing your tactics?
@Thijmen I am not sure if our solution is even the same problem that you and @rungta are having. I just know that it looked like it was the same at the time, so I figured sharing it might save someone else time. In our case, the 4GB was chosen based on the size of our database with extra wiggle room and resolved the issue, so we have not needed to test further. It knocked two queries from up to eight seconds to around half a second, which was acceptable, especially after caching. While they are still longer than any other queries, the next highest might be around 20-30ms, they are not painfully slow like before. We also did run the commands mentioned in this thread, which might have made a difference.
Have followed up with the OP @rungta and @JacobTrapp02 in support, with no resolution yet.
@Thijmen, if you want to email [email protected] with a database dump, as well as a SQL query that is performing slowly for you, we can investigate that, as well.
It would be great if you could share the output of the SQL SELECT version(); in that ticket.
Have followed up with the OP @rungta and @JacobTrapp02 in support, with no resolution yet.
@Thijmen, if you want to email [email protected] with a database dump, as well as a SQL query that is performing slowly for you, we can investigate that, as well.
It would be great if you could share the output of the SQL
SELECT version();in that ticket.
Hi @angrybrad, I've just mailed you the query as well (its in https://github.com/craftcms/cms/issues/17040 as well).
Hi we also have experienced a slower response time from CMS after updating craft to latest. .search() native and other 3rd party plugins like blitz cache is taking much longer to complete.
@yhlee-op check the two console command tips here as a starting point: https://craftcms.com/knowledge-base/troubleshooting-performance-issues#the-database
@angrybrad I am working with @yhlee-op on tis issue. Is there any settings I have to take in consideration for MariaDB? Version: 10.5.23-MariaDB Our charset is mixed on our database at the moment. Should these all be: CRAFT_DB_CHARSET="utf8mb4" CRAFT_DB_COLLATION="utf8mb4_unicode_ci"
I have run ./craft db/convert-charset
Which character set should be used? [utf8mb4]
Which collation should be used? [utf8mb4_unicode_ci]
Converting addresses ... done
Converting announcements ... done
Converting assetindexdata ... done
# truncated
Finished converting tables to utf8mb4/utf8mb4_unicode_ci.
After that, all tables are sharing the same collation.
And ./craft db/repair
Are you sure you want to repair all tables from the database? (yes|no) [no]:yes
Backup your database? (yes|no) [no]:yes
Backing up the database ... done
Repairing all database tables ...
# truncated
Finished repairing all database tables.
Is there anything else I can show you to identify the main issue in our case please? Debug tool? DB query run?
I have run a couple of queries before and after running the commands but the result is the same. No difference noticed
@aarodriguezfernandez
Is there any settings I have to take in consideration for MariaDB? Version: 10.5.23-MariaDB
10.5.23 is several years old - I'd suggest updating to a newer version. https://mariadb.org/mariadb/all-releases/
Our charset is mixed on our database at the moment. Should these all be: CRAFT_DB_CHARSET="utf8mb4" CRAFT_DB_COLLATION="utf8mb4_unicode_ci"
The important part is that they are all consistent. Those are both good defaults, but what they should be depends on the content your database stores. i.e. those might not work as well for Japanese/Chinese/Arabic characters, for example.
I have run a couple of queries before and after running the commands but the result is the same. No difference noticed
Assuming you ran db/repair after converting charsets/collations, you're still seeing the same slow behavior? If so, I'd highly suggest upgrading your MariaDB version - or even better, swap to a newer MySQL database version.