[4.x]: Super slow transaction query

samhibberd commented 5 months ago

What happened?

Description

Running a bunch of slow query testing on our site and found a really slow transaction query.

Steps to reproduce

Slow query logging uncovered this query, run it with a valid hash:

SELECT `amount`, `code`, `currency`, `dateCreated`, `dateUpdated`, `gatewayId`, `hash`, `id`, `message`, `note`, `orderId`, `parentId`, `paymentAmount`, `paymentCurrency`, `paymentRate`, `reference`, `response`, `status`, `type`, `userId`
FROM `commerce_transactions`
WHERE `hash`='testestestestetstestestestestest'
ORDER BY `id`
LIMIT 1;

Possible fix

Adding a hash index to the commerce_transactions table took it from 7s to under 10ms for us!

Craft CMS version

4.8.9

Craft Commerce version

4.5.3

PHP version

No response

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

-

linear[bot] commented 5 months ago

PT-1674 [4.x]: Super slow transaction query

samhibberd commented 5 months ago

Awesome thanks 🎉 @nfourtythree

nfourtythree commented 5 months ago

Hi @samhibberd

As it seems you have spotted this fix made it into version 4.6.0 of Commerce.

Thanks for raising the issue!

craftcms / commerce