cms icon indicating copy to clipboard operation
cms copied to clipboard

Create uid index on elements table

Open johnnynotsolucky opened this issue 5 years ago • 0 comments

This PR adds an index for the uid column on the elements table.

Some operations fetch elements by UID, for example, in Craft Commerce (3.0.1) when canceling a subscription, the uid is used, this results in a query similar to:

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `craft_elements` `elements`
INNER JOIN `craft_commerce_subscriptions` `commerce_subscriptions` ON `commerce_subscriptions`.`id` = `elements`.`id`
INNER JOIN `craft_users` `users` ON `commerce_subscriptions`.`userId` = `users`.`id`
INNER JOIN `craft_elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `craft_content` `content` ON `content`.`elementId` = `elements`.`id`
WHERE (`elements`.`uid`='<uid>') AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `commerce_subscriptions`.`dateCreated` DESC) `subquery`
INNER JOIN `craft_commerce_subscriptions` `commerce_subscriptions` ON `commerce_subscriptions`.`id` = `subquery`.`elementsId`
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_subscriptions`.`dateCreated` DESC
;

With over 3 million records, the query takes > 4s to execute.

After adding this index, it brings it down to a few milliseconds.

johnnynotsolucky avatar Jun 05 '20 15:06 johnnynotsolucky