[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

1. Seed around 40k to 60k entries on a Craft install
2. 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