database - Optimizing storage of Post Meta Entries

admin管理员组

文章数量:1122832

I'm working on a WooCommerce site where each order needs to update financial and order count data for a specific center, which are stored as post meta in another custom post type named "event". Each order is associated with a center, and after the order is processed, I need to update earnings and order counts. Currently, I'm considering two approaches to store and manage this data for about 700 centers:

Single Post Meta Array: Store all center data in one large array under a single post meta key of the "event" post type. Each element in the array represents a center with properties like name, earnings, and orders.

Separate Post Meta for Each Center: Use separate post meta entries for each center in the "event" post type. Each center's data (name, earnings, orders) would be stored in an individual array under a unique post meta key like center_{center_no}.

I'm concerned about the performance implications of both methods, especially with a large number of centers. The operations involve fetching the data, updating it, and saving it back to the database whenever an order is completed or processed.

Here's a simplified version of my current implementation using separate post meta for each center:

function update_earnings_on_event_post($order_id) {
    $order = wc_get_order($order_id);
    if (!$order) return;  // Exit if order is invalid

    $order_total = $order->get_total();
    $event_post_id = get_post_meta($order_id, 'event', true);

    if (get_post_type($event_post_id) !== 'event') {
        error_log('Invalid or missing event post ID for this order.');
        return;
    }

    $center_no = $order->get_meta('billing_center_no');
    $center_key = "center_{$center_no}";
    $center_data = get_post_meta($event_post_id, $center_key, true) ?: ['name' => $order->get_meta('billing_center_name'), 'earning' => 0, 'orders' => 0];

    // Update earnings and orders
    $center_data['earning'] += $order_total;
    $center_data['orders'] += 1;

    // Save updated data
    update_post_meta($event_post_id, $center_key, $center_data);
}

add_action('woocommerce_order_status_completed', 'update_earnings_on_event_post', 10, 1);
add_action('woocommerce_order_status_processing', 'update_earnings_on_event_post', 10, 1);

Could you provide insights or recommendations on which approach might be more scalable and efficient? Are there better strategies for handling such data related to WooCommerce order processing?

本文标签： databaseOptimizing storage of Post Meta Entries