[Performance] Search for orders using the transaction_id attribute instead of the _intent_id meta field.

[kalessil]

Issue: SQL query performance declines as the number of orders grows due to increased metadata volume. This in long term afffects HVMs during daily operations and BFCM during webhook surges.
Severity: medium

Currently, the following lines are present in \WC_Payments_Order_Service::attach_intent_info_to_order__legacy:

  $order->set_transaction_id( $intent_id );
  $this->set_intent_id_for_order( $order, $intent_id );

These lines result in the following behavior:

• With HPOS enabled: populated '_intent_id' meta and order table 'transaction_id' column.
• with CPT order storage: populated '_transaction_id' and '_intent_id' metas (holding the same value)
• Please also note WordPress.DB.SlowDBQuery.slow_db_query_meta_value suppression in \WC_Payments_DB::order_id_from_meta_key_value.

Recommended:

• Evaluate \WC_Payments_DB::order_id_from_meta_key_value using wc_get_orders with the transaction_id filter.
• Review the use of \WC_Payments_DB::order_from_charge_id and assess the feasibility of migrating to \WC_Payments_DB::order_from_intent_id.

With HPOS enabled, this approach removes the need for the underlying metas joins and reduces metas table bloat. Orders data migration may be required if the evaluation confirms feasibility.