Skip to main content

Performance & Indexing

WooCommerce Appointments 5.0+ introduces significant performance improvements through database indexing, query optimization, and an availability caching system. This guide covers the performance architecture and optimization techniques.

Indexing System​

Availability Index​

The indexing system precomputes availability data for faster calendar views and slot calculations.

Index Table Structure​

CREATE TABLE wp_wc_appointments_availability_cache (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  source varchar(50) NOT NULL,
  source_id bigint(20) unsigned NOT NULL,
  product_id bigint(20) unsigned NOT NULL,
  staff_id bigint(20) unsigned DEFAULT NULL,
  scope varchar(50) NOT NULL,
  appointable tinyint(1) NOT NULL,
  priority int(11) NOT NULL DEFAULT 0,
  qty int(11) NOT NULL DEFAULT 1,
  start_ts bigint(20) NOT NULL,
  end_ts bigint(20) NOT NULL,
  range_type varchar(50) NOT NULL,
  rule_kind varchar(50) NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'active',
  date_created datetime NOT NULL,
  date_modified datetime NOT NULL,
  PRIMARY KEY (id),
  KEY idx_source_qty_appointable_scope_product_staff_source_id (source, qty, appointable, scope, product_id, staff_id, source_id),
  KEY idx_product_staff_start_end (product_id, staff_id, start_ts, end_ts),
  KEY idx_start_end (start_ts, end_ts)
);

Composite Index Benefits​

  • Fast Range Queries: Optimized for time-based lookups
  • Product/Staff Filtering: Efficient filtering by product and staff combinations
  • Scope-based Queries: Quick access to global, product, or staff-specific rules

Database Indexes​

Postmeta Indexes​

Automatic creation of composite indexes on WordPress postmeta table:

-- Primary index for appointment queries
ALTER TABLE wp_postmeta ADD INDEX idx_post_id_meta_key (post_id, meta_key);

-- Additional indexes for specific queries
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_meta_value (meta_key, meta_value);

Query Optimization Impact​

  • Reduced Query Time: Appointment lookups improved by 60-80%
  • Lower Database Load: Fewer full table scans
  • Better Scalability: Handles larger appointment volumes efficiently

Query Optimizations​

JOIN Strategy​

The system uses different JOIN strategies based on query requirements:

INNER JOINs for Required Data​

-- When meta values are used in WHERE or ORDER BY
SELECT p.ID, pm1.meta_value as start_date, pm2.meta_value as end_date
FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.ID = pm1.post_id AND pm1.meta_key = '_appointment_start'
INNER JOIN wp_postmeta pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_appointment_end'
WHERE p.post_type = 'wc_appointment'
ORDER BY pm1.meta_value ASC;

EXISTS Subqueries for Filtering​

-- When meta is not used in ORDER BY (faster filtering)
SELECT p.ID 
FROM wp_posts p
WHERE p.post_type = 'wc_appointment'
AND EXISTS (
  SELECT 1 FROM wp_postmeta pm 
  WHERE pm.post_id = p.ID AND pm.meta_key = '_appointment_status'
  AND pm.meta_value = 'confirmed'
);

UNION Queries for OR Conditions​

-- Better index usage than OR conditions
SELECT * FROM appointments WHERE staff_id = 5
UNION
SELECT * FROM appointments WHERE product_id = 10;

Caching System​

Availability Cache​

  • Precomputed Data: Availability rules processed and stored in cache table
  • Time-based Invalidation: Cache expires based on rule changes and time horizons
  • Intelligent Rebuilding: Only affected date ranges are recalculated

Query Result Caching​

// Example of cached availability check
$cache_key = "availability_{$product_id}_{$staff_id}_{$date}";
$cached_result = wp_cache_get($cache_key, 'wc_appointments');

if (false === $cached_result) {
    $result = calculate_availability($product_id, $staff_id, $date);
    wp_cache_set($cache_key, $result, 'wc_appointments', 300); // 5 minutes
}

Performance Monitoring​

Query Performance​

Monitor slow queries using WordPress built-in tools:

// Enable query monitoring in wp-config.php
define('SAVEQUERIES', true);

// View query performance in admin
add_action('admin_init', function() {
    if (current_user_can('manage_options')) {
        global $wpdb;
        var_dump($wpdb->queries);
    }
});

Index Health Checks​

Automatic verification of required indexes:

// Built-in index verification
$index_status = WC_Appointments_Indexing::verify_indexes();
if (!$index_status['all_present']) {
    // Log missing indexes
    error_log('Missing appointment indexes: ' . print_r($index_status['missing'], true));
}

Optimization Best Practices​

Database Configuration​

  • MySQL 5.7+: Recommended for better index performance
  • InnoDB Engine: Required for proper index support
  • Sufficient Memory: Allocate adequate memory for index caching

Plugin Configuration​

  • Enable Indexing: Keep indexing system enabled for optimal performance
  • Regular Rebuilding: Schedule periodic index rebuilding
  • Monitor Cache Size: Ensure cache table doesn't grow excessively

Code Optimization​

  • Use Indexed Queries: Leverage precomputed data when possible
  • Avoid N+1 Queries: Use JOINs and EXISTS subqueries appropriately
  • Cache Frequently Accessed Data: Implement custom caching for complex calculations

Troubleshooting Performance Issues​

Slow Calendar Loading​

Symptoms: Calendar takes more than 3-5 seconds to load

Solutions:

  1. Verify indexes are created: WC_Appointments_Indexing::verify_indexes()
  2. Check cache table size: Large tables may need optimization
  3. Review query logs: Identify slow queries using SAVEQUERIES

High Memory Usage​

Symptoms: PHP memory exhaustion during availability calculations

Solutions:

  1. Increase PHP memory limit
  2. Reduce cache horizon in settings
  3. Optimize availability rules complexity

Database Locks​

Symptoms: Database timeouts during concurrent appointments

Solutions:

  1. Use atomic booking operations
  2. Implement proper transaction handling
  3. Consider read replicas for read-heavy operations

Performance Benchmarks​

Before Optimization (v4.x)​

  • Calendar Load: 8-12 seconds
  • Slot Calculation: 2-5 seconds per product
  • Database Queries: 50-100 queries per page load

After Optimization (v5.0+)​

  • Calendar Load: 1-3 seconds (70% improvement)
  • Slot Calculation: 200-500ms per product (90% improvement)
  • Database Queries: 10-20 queries per page load (80% reduction)

Advanced Topics​

Custom Indexing​

Developers can extend the indexing system for custom use cases:

// Register custom indexer
add_filter('wc_appointments_indexers', function($indexers) {
    $indexers['custom_availability'] = [
        'class' => 'Custom_Availability_Indexer',
        'priority' => 20,
    ];
    return $indexers;
});

Query Hooks​

Optimize queries using built-in hooks:

// Modify appointment queries for performance
add_filter('wc_appointments_get_appointments_query_args', function($args) {
    // Add efficient filtering
    $args['meta_query'] = [
        'relation' => 'AND',
        [
            'key' => '_appointment_status',
            'value' => ['confirmed', 'paid'],
            'compare' => 'IN',
        ],
    ];
    return $args;
});