SQL Query Logging on WooCommerce: checkout-profiler.php

<?php
/**
 * Plugin Name: Checkout Profiler
 * Description: Profiles WooCommerce checkout round-trip from Place Order to Thank You page.
 */
define('CHECKOUT_PROFILER_LOG', WP_CONTENT_DIR . '/checkout-profiler.log');
define('CHECKOUT_PROFILER_SQL_LOG', WP_CONTENT_DIR . '/checkout-profiler-sql.log');
define('CHECKOUT_PROFILER_SQL_DUMP', true); // set to false to disable SQL logging
define('CHECKOUT_PROFILER_LOG_MAX_BYTES', 5 * 1024 * 1024); // 5MB
global $checkout_profiler, $checkout_profiler_logging_sql;
$checkout_profiler = [];
$checkout_profiler_logging_sql = false;
// Utility: Write to main log
function checkout_profiler_log($msg) {
    $timestamp = date('[Y-m-d H:i:s]');
    $line = "$timestamp $msg" . PHP_EOL;
    $log_file = CHECKOUT_PROFILER_LOG;
    // Auto-rotate if too big
    if (file_exists($log_file) && filesize($log_file) > CHECKOUT_PROFILER_LOG_MAX_BYTES) {
        rename($log_file, $log_file . '.' . time() . '.bak');
    }
    file_put_contents($log_file, $line, FILE_APPEND);
}
// Utility: Write SQL query
add_filter('query', function ($query) {
    global $checkout_profiler_logging_sql;
    if ($checkout_profiler_logging_sql && defined('CHECKOUT_PROFILER_SQL_DUMP') && CHECKOUT_PROFILER_SQL_DUMP) {
        file_put_contents(CHECKOUT_PROFILER_SQL_LOG, $query . PHP_EOL, FILE_APPEND);
    }
    return $query;
});
// Log plugin init (for sanity check)
checkout_profiler_log('[log-test.php] MU plugin loaded at ' . date('H:i:s'));
// On WooCommerce checkout page load
add_action('template_redirect', function () {
    if (!function_exists('is_checkout') || !is_checkout()) return;
    global $checkout_profiler, $checkout_profiler_logging_sql;
    $checkout_profiler = [
        'start_time' => microtime(true),
        'start_memory' => memory_get_usage(),
        'start_queries' => get_num_queries(),
        'start_query_time' => (defined('SAVEQUERIES') && SAVEQUERIES && isset($GLOBALS['wpdb']->queries)) ?
            array_sum(array_column($GLOBALS['wpdb']->queries, 1)) : 0,
    ];
    $checkout_profiler_logging_sql = true;
    checkout_profiler_log('[Checkout Profiler] Checkout profiler started');
});
// Shutdown: after page finishes rendering
add_action('shutdown', function () {
    global $checkout_profiler, $checkout_profiler_logging_sql, $wpdb;
    if (empty($checkout_profiler['start_time'])) return;
    $end_time = microtime(true);
    $end_memory = memory_get_usage();
    $end_queries = get_num_queries();
    $duration = $end_time - $checkout_profiler['start_time'];
    $memory_used = ($end_memory - $checkout_profiler['start_memory']) / 1024 / 1024;
    $query_count = $end_queries - $checkout_profiler['start_queries'];
    $query_time_total = 0;
    if (defined('SAVEQUERIES') && SAVEQUERIES && isset($GLOBALS['wpdb']->queries)) {
        $query_time_total = array_sum(array_column($GLOBALS['wpdb']->queries, 1)) - ($checkout_profiler['start_query_time'] ?? 0);
    }
    checkout_profiler_log(sprintf(
        '[Checkout Profiler] Total time: %.2fs | Queries: %d | Query time: %.2fs | Memory: %.2f MB',
        $duration,
        $query_count,
        $query_time_total,
        $memory_used
    ));
    $checkout_profiler_logging_sql = false;
});
// Optional: log extra key WooCommerce hooks
add_action('woocommerce_checkout_order_processed', function ($order_id) {
    checkout_profiler_log("[Hook] woocommerce_checkout_order_processed - Order ID: $order_id");
}, 10, 1);
add_action('woocommerce_thankyou', function ($order_id) {
    checkout_profiler_log("[Hook] woocommerce_thankyou - Order ID: $order_id");
}, 10, 1);