Slow `wp-json/pronamic-pay/mollie/v1/webhook` REST API endpoint

[remcotolsma]

If the wp_postmeta table contains 3.000.000+ items the wp-json/pronamic-pay/mollie/v1/webhook REST API endpoint is very slow, we had a call from Mollie about this issue. The webhook is slow because the wp_postmeta table has to be queried to find the payment ID based on the Mollie transaction ID (for example: tr_7UhSN1zuXS). To solve this we had the idea to use the as_enqueue_async_action() function from the https://actionscheduler.org/ library. On second thought that's probably not the best solution. Perhaps the easiest for now is to add the payment ID to the webhook URL:

wp-json/pronamic-pay/mollie/v1/webhook/123

https://github.com/wp-pay-gateways/mollie/blob/a65e919f6228483c5672be6bdfdb3ae64342975f/src/Gateway.php#L233-L263

Another solution is to introduce custom tables with on index on the payment provider transaction ID field.

CREATE TABLE wp_pronamic_pay_payments (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT(20) UNSIGNED NOT NULL UNIQUE,
    transaction_number VARCHAR(255) INDEX
) ENGINE=INNODB;

• https://github.com/pronamic/wp-pronamic-pay/issues/234

@rvdsteege What do you think about the wp-json/pronamic-pay/mollie/v1/webhook/(?P<payment_id>\d+) approach?

[rvdsteege]

I prefer adding the payment ID to the webhook URL (doesn't hurt anyone) and then take some time in the (near) future to completely move to custom tables.

[remcotolsma]

Added in https://github.com/wp-pay-gateways/mollie/commit/62714ce56746c912762eee87468ea39891b74971, @rvdsteege can you do a review?

http --form POST http://pay.local/wp-json/pronamic-pay/mollie/v1/webhook id=tr_7UhSN1zuXS

http --form POST http://pay.local/wp-json/pronamic-pay/mollie/v1/webhook/1 id=tr_7UhSN1zuXS

[rvdsteege]

Working as expected, LGTM 👍