Cms: revisionId filter in ElementQuery adds considerable execution time

[johnnynotsolucky]

Description

The filter for null revisionId's here https://github.com/craftcms/cms/blob/develop/src/elements/db/ElementQuery.php#L2455 can at least double query execution time for element types with a large number of records.

For example, the get-elements query for Orders in Craft Commerce is something like:

SELECT `elements`.`id`, ... -- every field known to man
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_commerce_orders` `commerce_orders` ON `commerce_orders`.`id` = `elements`.`id`
LEFT JOIN `craft_commerce_addresses` `billing_address` ON billing_address.id = `commerce_orders`.`billingAddressId`
LEFT JOIN `craft_commerce_addresses` `shipping_address` ON shipping_address.id = `commerce_orders`.`shippingAddressId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON `content`.`elementId` = `elements`.`id`
WHERE (`commerce_orders`.`orderStatusId`='2') AND (`elements`.`archived`=0) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `commerce_orders`.`dateUpdated`
LIMIT 100) `subquery`
INNER JOIN `craft_commerce_orders` `commerce_orders` ON `commerce_orders`.`id` = `subquery`.`elementsId`
LEFT JOIN `craft_commerce_addresses` `billing_address` ON billing_address.id = `commerce_orders`.`billingAddressId`
LEFT JOIN `craft_commerce_addresses` `shipping_address` ON shipping_address.id = `commerce_orders`.`shippingAddressId`
INNER JOIN `craft_elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `craft_content` `content` ON `content`.`id` = `subquery`.`contentId`
ORDER BY `commerce_orders`.`dateUpdated`;

This takes anywhere from > 8s consistently. Removing the revisionId IS NULL filter brings this down to ~800ms. This is the same for non-commerce related element types.

In this database there are just under 500k users, 550k orders and 400k subscriptions.

AFAICT only Entries support revisions at this point. Can element types which _don't_ need to handle revisions be explicit about it and drop this filter altogether for them?

Steps to reproduce

1. Login into CP
2. Navigate to Users (450k users)
3. Observe get-elements and count-elements call takes a while.

Additional info

• Craft version: 3.4.9
• PHP version: 7.3
• Database driver & version: MariaDB 10.3
• Plugins & versions: Commerce 3.0.1

[brandonkelly]

AFAICT only Entries support revisions at this point. Can element types which _don't_ need to handle revisions be explicit about it and drop this filter altogether for them?

That’s one option, but there’s plenty of sites with 100,000’s/millions of entries as well. so we should be solving this in a way that speeds up entries in addition to users and other element types.

I think the main issue here is that MySQL doesn’t use its draftId / revisionId indexes when you are searching for null values, since it only indexes non-null values. Just PR’d #5895, which adds a new boolean isSource column to the elements table, and replaces the draftId is null and revisionId is null condition for isSource = true, which should fix that. We’ll do a little testing to verify there’s a noticeable improvement, and then pull it in for 3.5.

[angrybrad]

@johnnynotsolucky any chance we could get a database dump and a composer.json file to do some testing with on this sent to [email protected]?

[johnnynotsolucky]

Sorry @angrybrad, no can do. The production database has sensitive data, and the staging database doesn't have the same size of data.

I'm sure you could load random data into the various elements and achieve the same thing? We don't really have an extra fields on our elements. The orders table has a matrix field attached with 2 plain text fields and a dropdown.

[brandonkelly]

@johnnynotsolucky We’re not able to reproduce, and my PR isn’t showing definitive results (if anything, potentially a little worse).

Any chance you can help test? If so, please do this:

1. Create a database backup.
2. Create a new database, and restore your backup to it.
3. Temporarily point your local Craft install over to the new database.
4. Change your craftcms/cms constraint in composer.json to "dev-feature/isSource".
5. Run composer update.
6. Visit your control panel to run the new migration(s), or run ./craft migrate/up --type=app.
7. Test whether the change has improved performance of order queries.

If that doesn’t help, point your Craft install back to the original database, but please keep the new one around, so we can try some other things.

[johnnynotsolucky]

@brandonkelly sorry for responding so late. I don't have capacity to test this out right now, @sjcallender mentioned that he should be able to though.