Optimizing p99 database query response latency in multi-site Factory Method design structures custom tables

Leveraging Factory Methods for Efficient Custom Table Querying in Multisite WordPress

Optimizing the p99 (99th percentile) response latency for database queries is paramount in high-traffic WordPress multisite environments, especially when dealing with custom data structures. This post delves into advanced techniques for managing custom database tables within a multisite setup, focusing on the strategic application of the Factory Method design pattern to streamline query execution and minimize latency.

We’ll assume a scenario where a plugin manages custom data across multiple sites, necessitating efficient retrieval and manipulation of this data. The core challenge lies in abstracting the site-specific database context while maintaining performant query patterns. This often involves custom tables that mirror or extend core WordPress data, but are managed independently by the plugin.

Designing the Factory for Site-Specific Table Access

The Factory Method pattern provides a clean way to encapsulate the instantiation of objects responsible for interacting with database tables. In a multisite context, this means creating factories that can produce table accessors tailored to the current site’s database prefix. This abstraction prevents direct coupling to `wpdb` with hardcoded table names and prefixes, making the code more maintainable and adaptable.

Consider a base class for our table accessors, which handles common logic and defines an interface for CRUD operations. Then, concrete implementations will be generated by a factory, ensuring the correct table names (including site-specific prefixes) are used.

Base Table Accessor Interface and Abstract Factory

We’ll start by defining an abstract class or interface for our table accessors. This sets the contract for how we interact with our custom data.

abstract class AbstractCustomTableAccessor {
    protected $wpdb;
    protected $table_name;

    public function __construct(wpdb $wpdb, string $table_name) {
        $this->wpdb = $wpdb;
        $this->table_name = $table_name;
    }

    abstract public function get_by_id(int $id);
    abstract public function insert(array $data);
    abstract public function update(int $id, array $data);
    abstract public function delete(int $id);
    abstract public function get_all();
    abstract public function get_paginated(int $per_page, int $page_number, array $args = []);
}

Next, the abstract factory class. This class will define the factory method, which concrete subclasses will implement to return specific accessor instances.

abstract class CustomTableAccessorFactory {
    protected $wpdb;

    public function __construct(wpdb $wpdb) {
        $this->wpdb = $wpdb;
    }

    /**
     * The factory method.
     *
     * @return AbstractCustomTableAccessor
     */
    abstract protected function createAccessor(): AbstractCustomTableAccessor;

    /**
     * The client code calls this method to get a product object.
     *
     * @return AbstractCustomTableAccessor
     */
    public function getAccessor(): AbstractCustomTableAccessor {
        // The factory method may create a default product or
        // delegate to a subclass to create a product.
        $accessor = $this->createAccessor();
        // Additional initialization logic can be added here if needed.
        return $accessor;
    }
}

Concrete Factory and Accessor for Multisite

Now, we create a concrete factory that understands how to construct the correct table name for the current WordPress site. This factory will use `get_blog_prefix()` to dynamically determine the database prefix.

class MultisiteCustomTableAccessorFactory extends CustomTableAccessorFactory {
    private $base_table_name;

    public function __construct(wpdb $wpdb, string $base_table_name) {
        parent::__construct($wpdb);
        $this->base_table_name = $base_table_name;
    }

    /**
     * The concrete factory method.
     *
     * @return AbstractCustomTableAccessor
     */
    protected function createAccessor(): AbstractCustomTableAccessor {
        $blog_prefix = $this->wpdb->get_blog_prefix();
        $full_table_name = $blog_prefix . $this->base_table_name;

        // In a real-world scenario, you might have different implementations
        // based on table structure or specific needs. For simplicity, we use one.
        return new ConcreteCustomTableAccessor($this->wpdb, $full_table_name);
    }
}

And the concrete accessor implementation. This class will contain the actual SQL queries, ensuring they use the fully qualified table name passed during instantiation.

class ConcreteCustomTableAccessor extends AbstractCustomTableAccessor {

    public function get_by_id(int $id) {
        $sql = $this->wpdb->prepare(
            "SELECT * FROM {$this->table_name} WHERE id = %d",
            $id
        );
        return $this->wpdb->get_row($sql);
    }

    public function insert(array $data) {
        // Assuming $data keys map directly to table columns.
        // This is a simplified example; robust implementation would validate keys.
        $inserted = $this->wpdb->insert($this->table_name, $data);
        if ($inserted) {
            return $this->wpdb->insert_id;
        }
        return false;
    }

    public function update(int $id, array $data) {
        return $this->wpdb->update($this->table_name, $data, ['id' => $id]);
    }

    public function delete(int $id) {
        return $this->wpdb->delete($this->table_name, ['id' => $id]);
    }

    public function get_all() {
        $sql = "SELECT * FROM {$this->table_name}";
        return $this->wpdb->get_results($sql);
    }

    public function get_paginated(int $per_page, int $page_number, array $args = []) {
        $offset = ($page_number - 1) * $per_page;
        $where_clauses = [];
        $where_values = [];

        // Example: Add WHERE clauses from $args
        if (!empty($args['status'])) {
            $where_clauses[] = "status = %s";
            $where_values[] = sanitize_text_field($args['status']);
        }
        if (!empty($args['user_id'])) {
            $where_clauses[] = "user_id = %d";
            $where_values[] = intval($args['user_id']);
        }

        $sql = "SELECT * FROM {$this->table_name}";
        if (!empty($where_clauses)) {
            $sql .= " WHERE " . implode(' AND ', $where_clauses);
        }
        $sql .= $this->wpdb->prepare(" ORDER BY id DESC LIMIT %d OFFSET %d", $per_page, $offset);

        // Prepare the full query with values if any WHERE clauses exist
        if (!empty($where_values)) {
            $sql = $this->wpdb->prepare($sql, $where_values);
        }

        $results = $this->wpdb->get_results($sql);
        
        // Get total count for pagination
        $count_sql = "SELECT COUNT(*) FROM {$this->table_name}";
        if (!empty($where_clauses)) {
            $count_sql .= " WHERE " . implode(' AND ', $where_clauses);
            // Prepare the count query with values if any WHERE clauses exist
            if (!empty($where_values)) {
                $count_sql = $this->wpdb->prepare($count_sql, $where_values);
            }
        }
        $total_items = $this->wpdb->get_var($count_sql);

        return [
            'results' => $results,
            'total_items' => (int) $total_items,
            'total_pages' => ceil($total_items / $per_page)
        ];
    }
}

Integrating the Factory in a Multisite Plugin

The key to leveraging this pattern is to instantiate the factory and then use it to obtain the accessor. This should happen within the context of a request, ensuring the correct site’s `wpdb` object is used.

A common place to initialize this would be in your plugin’s main class or a service container if you’re using one. Ensure you’re using the global `$wpdb` instance.

/**
 * Example of how to use the factory within your plugin.
 */
class MyMultisitePlugin {
    private $custom_data_accessor;
    private $custom_data_factory;
    private $wpdb;

    const MY_CUSTOM_TABLE = 'my_plugin_data'; // Base name without prefix

    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;

        // Initialize the factory for the current site
        $this->custom_data_factory = new MultisiteCustomTableAccessorFactory($this->wpdb, self::MY_CUSTOM_TABLE);
        $this->custom_data_accessor = $this->custom_data_factory->getAccessor();

        // Hook into WordPress actions/filters to use the accessor
        add_action('some_plugin_action', [$this, 'process_data']);
        add_action('rest_api_init', [$this, 'register_rest_routes']);
    }

    public function process_data() {
        // Example: Inserting data
        $new_data = [
            'user_id' => get_current_user_id(),
            'meta_key' => 'some_value',
            'created_at' => current_time('mysql', 1)
        ];
        $inserted_id = $this->custom_data_accessor->insert($new_data);

        if ($inserted_id) {
            // Example: Retrieving data
            $item = $this->custom_data_accessor->get_by_id($inserted_id);
            error_log('Inserted and retrieved item: ' . print_r($item, true));
        }
    }

    public function register_rest_routes() {
        register_rest_route('myplugin/v1', '/data/(?P<id>\d+)', [
            'methods' => 'GET',
            'callback' => [$this, 'get_custom_data_item'],
            'permission_callback' => '__return_true' // Implement proper permissions
        ]);
        register_rest_route('myplugin/v1', '/data', [
            'methods' => 'GET',
            'callback' => [$this, 'get_paginated_custom_data'],
            'permission_callback' => '__return_true' // Implement proper permissions
        ]);
    }

    public function get_custom_data_item(WP_REST_Request $request) {
        $id = (int) $request->get_param('id');
        $item = $this->custom_data_accessor->get_by_id($id);
        if (!$item) {
            return new WP_Error('not_found', 'Item not found', ['status' => 404]);
        }
        return new WP_REST_Response($item);
    }

    public function get_paginated_custom_data(WP_REST_Request $request) {
        $per_page = $request->get_param('per_page') ?: 10;
        $page = $request->get_param('page') ?: 1;
        $status = $request->get_param('status');
        $user_id = $request->get_param('user_id');

        $args = [];
        if ($status) $args['status'] = $status;
        if ($user_id) $args['user_id'] = $user_id;

        $data = $this->custom_data_accessor->get_paginated($per_page, $page, $args);
        return new WP_REST_Response($data);
    }
}

// Instantiate the plugin on WordPress initialization
// Ensure this is called at the right time, e.g., within a plugin's main file.
// global $my_multisite_plugin;
// $my_multisite_plugin = new MyMultisitePlugin();

Performance Considerations for p99 Latency

While the Factory Method pattern improves code organization and maintainability, direct query optimization is crucial for p99 latency. Here are key strategies:

• Indexing: Ensure all columns used in WHERE, ORDER BY, and JOIN clauses are properly indexed. For the get_paginated method, indexes on id, status, and user_id would be beneficial.

-- Example: Add index for status and user_id for paginated queries
ALTER TABLE `wp_your_site_prefix_my_plugin_data` ADD INDEX `idx_status_user_id` (`status`, `user_id`);
-- Or a composite index if queries frequently filter by both
ALTER TABLE `wp_your_site_prefix_my_plugin_data` ADD INDEX `idx_user_id_status` (`user_id`, `status`);

• Query Optimization: Avoid SELECT * in production code if only a few columns are needed. Explicitly list the required columns.

    // Inside ConcreteCustomTableAccessor::get_all()
    public function get_all() {
        // Specify columns for better performance if not all are needed
        $sql = "SELECT id, user_id, meta_key FROM {$this->table_name}";
        return $this->wpdb->get_results($sql);
    }

• Caching: Implement object caching (e.g., Redis, Memcached) for frequently accessed, rarely changing data. WordPress’s Transients API can be a starting point, but for custom tables, direct integration with a caching layer is more effective.

For instance, caching the results of get_all() or specific get_by_id() calls can drastically reduce database load. Be mindful of cache invalidation when data is updated or deleted.

class CachedCustomTableAccessor extends ConcreteCustomTableAccessor {
    private $cache_group;
    private $cache_ttl;

    public function __construct(wpdb $wpdb, string $table_name, string $cache_group = 'custom_table_data', int $cache_ttl = HOUR_IN_SECONDS) {
        parent::__construct($wpdb, $table_name);
        $this->cache_group = $cache_group;
        $this->cache_ttl = $cache_ttl;
    }

    private function get_cache_key(string $method, $args = []) {
        return $this->cache_group . ':' . md5(json_encode(array_merge([$method], (array) $args)));
    }

    public function get_by_id(int $id) {
        $cache_key = $this->get_cache_key('get_by_id', $id);
        $cached_data = wp_cache_get($cache_key, $this->cache_group);

        if (false !== $cached_data) {
            return $cached_data;
        }

        $data = parent::get_by_id($id);
        if ($data) {
            wp_cache_set($cache_key, $data, $this->cache_group, $this->cache_ttl);
        }
        return $data;
    }

    public function insert(array $data) {
        $this->clear_cache(); // Invalidate relevant cache entries
        return parent::insert($data);
    }

    public function update(int $id, array $data) {
        $this->clear_cache(); // Invalidate relevant cache entries
        return parent::update($id, $data);
    }

    public function delete(int $id) {
        $this->clear_cache(); // Invalidate relevant cache entries
        return parent::delete($id);
    }

    // A more sophisticated cache invalidation strategy would be needed for get_paginated
    // For simplicity, we'll clear all for now.
    private function clear_cache() {
        wp_cache_flush_group($this->cache_group);
    }
}

To use the cached version, you would modify the factory or the plugin’s instantiation logic:

// Inside MyMultisitePlugin constructor:
// ...
$this->custom_data_accessor = new CachedCustomTableAccessor($this->wpdb, $full_table_name);
// ...

• Database Connection Pooling: While WordPress core doesn’t natively support connection pooling for its main database connection, for very high-load scenarios or external database services, consider solutions that manage persistent connections or optimize connection establishment. This is often outside the scope of typical plugin development but is a factor in extreme performance tuning.

• Read Replicas: For read-heavy workloads, configure WordPress to use database read replicas. This requires modifying wp-config.php and ensuring your plugin’s queries are directed appropriately. The factory pattern can be extended to select the correct `wpdb` instance (master vs. replica) based on the operation type (read vs. write).

Implementing read replicas involves defining constants for replica databases in wp-config.php and then using `wpdb`’s ability to switch connections. Your factory could be adapted to accept a connection type parameter.

// In wp-config.php (example)
define( 'WP_USE_EXT_DB', true );
$GLOBALS['wpdb']->add_database( array(
    'host'     => 'localhost',
    'user'     => 'user',
    'password' => 'password',
    'name'     => 'database',
    'prefix'   => 'wp_',
) );
$GLOBALS['wpdb']->add_database( array(
    'host'     => 'replica.db.host',
    'user'     => 'replica_user',
    'password' => 'replica_password',
    'name'     => 'database',
    'prefix'   => 'wp_',
    'read_only' => true, // Important for replicas
) );

// Modified Factory to handle read/write
class MultisiteReadWriteCustomTableAccessorFactory extends MultisiteCustomTableAccessorFactory {
    private $use_replica = false;

    public function __construct(wpdb $wpdb, string $base_table_name, bool $use_replica = false) {
        parent::__construct($wpdb, $base_table_name);
        $this->use_replica = $use_replica;
    }

    protected function createAccessor(): AbstractCustomTableAccessor {
        $blog_prefix = $this->wpdb->get_blog_prefix();
        $full_table_name = $blog_prefix . $this->base_table_name;

        // Select the appropriate database connection
        if ($this->use_replica) {
            // Assuming $this->wpdb is configured to use replicas
            // WordPress's internal logic for read/write splitting is complex.
            // For custom tables, you might need to manually switch connections
            // or use a library that abstracts this.
            // A simplified approach:
            $this->wpdb->select_database( $this->wpdb->get_database( true ) ); // Select replica DB
        } else {
            $this->wpdb->select_database( $this->wpdb->get_database( false ) ); // Select primary DB
        }

        return new ConcreteCustomTableAccessor($this->wpdb, $full_table_name);
    }
}

// Usage:
// $read_factory = new MultisiteReadWriteCustomTableAccessorFactory($wpdb, self::MY_CUSTOM_TABLE, true);
// $read_accessor = $read_factory->getAccessor();
// $data = $read_accessor->get_all(); // This query would go to the replica

Conclusion

By employing the Factory Method pattern, you create a robust and maintainable system for managing custom database tables in a WordPress multisite environment. This abstraction layer, combined with diligent query optimization, indexing, and caching strategies, is essential for achieving and sustaining low p99 response latencies. Remember that performance tuning is an ongoing process, and continuous monitoring and profiling are key to identifying and resolving bottlenecks.