[5.x]: Default entry order by not using indexes

[Wiejeben]

What happened?

Description

Hi I'm currently having the same issues, the "Using where; Using temporary; Using filesort" is causing significant load.

Steps to reproduce

To isolate the issue I've created a simple test.twig with only

{% set entries = craft.entries().section('products').limit(3).all() %}

This adds a query that takes 150 ms to 500 ms, sometimes up to 1.8 seconds depending on server load.

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`=2) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2025-01-08 13:01:59') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2025-01-08 13:01: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 3) `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

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		NULL	ALL	NULL	NULL	NULL	NULL	3	100	Using temporary; Using filesort
1	PRIMARY	elements_sites	NULL	eq_ref	PRIMARY	PRIMARY	4	subquery.siteSettingsId	1	100	NULL
1	PRIMARY	entries	NULL	eq_ref	PRIMARY	PRIMARY	4	subquery.elementsId	1	100	NULL
1	PRIMARY	elements	NULL	eq_ref	PRIMARY	PRIMARY	4	subquery.elementsId	1	100	NULL
2	DERIVED	entries	NULL	ref	PRIMARY,entries_postDate_idx,entries_expiryDate_idx,entries_sectionId_idx	entries_sectionId_idx	5	const	68894	25	Using where; Using temporary; Using filesort
2	DERIVED	elements	NULL	eq_ref	PRIMARY,elements_dateDeleted_idx,elements_enabled_idx,elements_archived_dateCreated_idx,elements_draftId_fk,elements_revisionId_fk,idx_ysodvsrchxwcbrhnowgfcjhhkluvskzrxjrv,idx_rfmcmvevidwektcnhwcayzlavagmjumpudir	PRIMARY	4	sneakersquad_production.entries.id	1	5	Using where
2	DERIVED	elements_sites	NULL	ref	elements_sites_elementId_siteId_unq_idx,elements_sites_enabled_idx	elements_sites_elementId_siteId_unq_idx	4	sneakersquad_production.entries.id	1	50	Using where

Same thing happens with structures except this is a bit more complicated:

{% set entries = craft.entries().section('models').limit(3).all() %}

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`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
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`
LEFT JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `elements`.`id`) AND (`structureelements`.`structureId`=14)
WHERE (`entries`.`sectionId`=21) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2025-01-08 14:42:59') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2025-01-08 14:42:59'))) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC, `elements`.`id` DESC
LIMIT 3) `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`
LEFT JOIN `structureelements` `structureelements` ON (`structureelements`.`elementId` = `subquery`.`elementsId`) AND (`structureelements`.`structureId`=14)
ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC, `elements`.`id` DESC

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		NULL	ALL	NULL	NULL	NULL	NULL	3	100	Using temporary; Using filesort
1	PRIMARY	elements_sites	NULL	eq_ref	PRIMARY	PRIMARY	4	subquery.siteSettingsId	1	100	NULL
1	PRIMARY	entries	NULL	eq_ref	PRIMARY	PRIMARY	4	subquery.elementsId	1	100	NULL
1	PRIMARY	elements	NULL	eq_ref	PRIMARY	PRIMARY	4	subquery.elementsId	1	100	NULL
1	PRIMARY	structureelements	NULL	eq_ref	structureelements_structureId_elementId_unq_idx,structureelements_elementId_idx	structureelements_structureId_elementId_unq_idx	9	const,subquery.elementsId	1	100	NULL
2	DERIVED	entries	NULL	ref	PRIMARY,entries_postDate_idx,entries_expiryDate_idx,entries_sectionId_idx	entries_sectionId_idx	5	const	103056	25	Using where; Using temporary; Using filesort
2	DERIVED	elements	NULL	eq_ref	PRIMARY,elements_dateDeleted_idx,elements_enabled_idx,elements_archived_dateCreated_idx,elements_draftId_fk,elements_revisionId_fk,idx_ysodvsrchxwcbrhnowgfcjhhkluvskzrxjrv,idx_rfmcmvevidwektcnhwcayzlavagmjumpudir	PRIMARY	4	sneakersquad_production.entries.id	1	5	Using where
2	DERIVED	elements_sites	NULL	ref	elements_sites_elementId_siteId_unq_idx,elements_sites_enabled_idx	elements_sites_elementId_siteId_unq_idx	4	sneakersquad_production.entries.id	1	50	Using where
2	DERIVED	structureelements	NULL	eq_ref	structureelements_structureId_elementId_unq_idx,structureelements_elementId_idx	structureelements_structureId_elementId_unq_idx	9	const,sneakersquad_production.entries.id	1	100	NULL

Expected behavior

I've manually tried to execute the query to find out what exactly is causing indexes not being used, and this was specifically because of the ORDER BY on line 7 (not the ORDER BY on the last line).

Changing the ORDER BY from

ORDER BY `entries`.`postDate` DESC, `elements`.`id` DESC

To

ORDER BY `entries`.`postDate` DESC, `entries`.`id` DESC

Would speeding up the query to 2-3 ms. Looks like the secondary ORDER BY was added in Craft 4.8.2 (https://github.com/craftcms/cms/blob/4.x/CHANGELOG.md#482---2024-03-12). I don't really know of a solution for structures.

Craft CMS version

5.5.9

PHP version

8.2.8

Operating system and version

Docker

Database type and version

MySQL 9.0.1

Image driver and version

Imagick 3.7.0 (ImageMagick 7.1.1-13)

Installed plugins and versions

Blitz 5.9.9
CKEditor 4.4.0
Craftagram 4.1.0
GeoMate 3.0.0
Hyper 2.2.2
oEmbed 3.1.3
Retour 5.0.4
SEOmatic 5.1.8
Smith 3.1.0
Views Work 2.2.1
Vite 5.0.1

[brandonkelly]

Any chance you can send a database backup and your Composer files over to [email protected]?