Cms: Default order() can be slow on large datasets

[markhuot]

Description

When dealing with a large dataset the default Entry::find() SQL generates a sort that includes the structureelements table as well as the entries table. EXPLAIN tells me that's generating a temporary table and a file sort which is slowing things down. If I manually add ->order('postDate desc') to my query the time drops dramatically because MySQL no longer needs the temporary table/filesort.

What's interesting about all this is I'm limiting my query to a ->section() that doesn't have a structure to it so that extra order won't ever do anything for this particular query.

The FR here is: could the query builder be smart enough to drop the structure stuff when it's not necessary?

Steps to reproduce

1. Install a large database of entries. I'm working with 3.1M rows
2. Run a \craft\elements\Entry::find()->getRawSql() and notice that the query is built with an ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC clause
3. Update your query to specify ->order('postDate DESC') and notice the query is now much faster

Additional info

• Craft version: 3.4.6.1
• PHP version: 7.3
• Database driver & version: MySQL 5.7
• Plugins & versions: n/a

[brandonkelly]

What's interesting about all this is I'm limiting my query to a ->section() that doesn't have a structure to it so that extra order won't ever do anything for this particular query.

Good point. There was actually a hint in the logic that the intention was to avoid joining structure data when setting the section param to a non-Structure section – see how structureId would get set to false in that case:

https://github.com/craftcms/cms/blob/eca660ae7dde806bba54cc69d7a67d50e7745a76/src/elements/db/EntryQuery.php#L288-L289

But ElementQuery::_shouldJoinStructureData() doesn’t care if structureId is false if withStructure is true.

https://github.com/craftcms/cms/blob/eca660ae7dde806bba54cc69d7a67d50e7745a76/src/elements/db/ElementQuery.php#L2182-L2188

Just fixed the logic for the next release, so section() now sets withStructure = false.

To get the fix early, change your craftcms/cms requirement in composer.json to:

"require": {
  "craftcms/cms": "dev-develop#ea6111552e85be4d585ff0c5cac5229b1e010ea8 as 3.4.8",
  "...": "..."
}

Then run composer update.

[Mosnar]

Nice! Linking this old related issue of mine for the sake of a paper trail: #5239

[markhuot]

This is so exciting, thanks Brandon! I'll be testing dev-develop today.

FYI, by manually setting the .orderBy logic in my templates on _a few_ queries I was able to take my homepage from 8s+ down to 1s. Can't wait to see this applied to all queries, not just the ones I manually set.

[brandonkelly]

Craft 3.4.9 is out now with this change.