cms
cms copied to clipboard
Create uid index on elements table
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.