Skip to main content

Database Query Logging

Learn how to monitor and debug database queries for performance optimization.

Enabling Query Logging​

To enable database query logging for GSM Middleware REST API endpoints, add these constants to your wp-config.php file (before the "That's all, stop editing!" line):

define( 'GSM_LOG_QUERIES', true );
define( 'SAVEQUERIES', true );
define( 'WP_DEBUG', true );
define( 'WP_DEBUG_LOG', true );
define( 'WP_DEBUG_DISPLAY', false );

Viewing Query Logs​

Query logs are written to your WordPress debug log at: wp-content/debug.log

Each REST API request that has query logging enabled will output a formatted log showing:

  • Total number of queries
  • Individual query SQL
  • Query execution time
  • Call stack (if available)

What Gets Logged​

Orders Page (/wp-admin/admin.php?page=gsm-orders)​

When the Orders page loads, it makes the following REST API calls:

1. GET /wp-json/gsm-middleware/v1/sites​

  • Timing: Deferred 200ms after page load
  • Purpose: Load sites for filter dropdown
  • Queries: 1 query to rm_sites table
  • Caching: 5-minute browser cache

2. GET /wp-json/gsm-middleware/v1/orders​

  • Timing: Immediate on page load
  • Purpose: Load paginated order list (20 items per page)
  • Queries:
    • COUNT query for pagination total
    • SELECT query with LEFT JOIN to rm_sites

Items Page (/wp-admin/admin.php?page=gsm-items)​

Similar structure to Orders page:

  • Sites endpoint (deferred)
  • Items endpoint with pagination

Example Log Output​

When query logging is enabled, you'll see output like this in debug.log:

═══════════════════════════════════════════════════════════════
Query Log: GET /gsm-middleware/v1/orders
Total Queries: 2
═══════════════════════════════════════════════════════════════

Query #1 (0.0023s):
SELECT COUNT(*) FROM `rm_order` WHERE 1=1

Query #2 (0.0156s):
SELECT o.*, s.name as site_name 
FROM `rm_order` o 
LEFT JOIN `rm_sites` s ON o.site = s.id 
WHERE 1=1 
ORDER BY o.dateadded DESC 
LIMIT 20 OFFSET 0

Total Time: 0.0179s
═══════════════════════════════════════════════════════════════

Performance Benchmarks​

Without Performance Indexes​

  • COUNT query: 0.050-0.500 seconds
  • SELECT query: 0.100-1.000 seconds
  • Total: 0.150-1.500 seconds

With Migration 011 Indexes​

  • COUNT query: 0.001-0.010 seconds (~50x faster)
  • SELECT query: 0.005-0.050 seconds (~20x faster)
  • Total: 0.006-0.060 seconds

Disabling Query Logging​

To disable query logging in production:

// Comment out or remove:
// define( 'GSM_LOG_QUERIES', true );

Keep WP_DEBUG_LOG enabled to capture other errors, but remove the query logging flag to reduce log file size.

Troubleshooting​

No Queries Showing Up​

Check that SAVEQUERIES is enabled:

define( 'SAVEQUERIES', true );

Verify debug logging is working:

define( 'WP_DEBUG_LOG', true );

Check file permissions:

chmod 644 wp-content/debug.log

Too Many Queries​

If you see more queries than expected:

  1. Check for plugin hooks - Other plugins may be hooking into REST API requests
  2. Verify caching - Sites endpoint should be cached for 5 minutes
  3. Look for N+1 problems - Multiple queries in loops indicate inefficient code
  4. Check database indexes - Missing indexes cause slow queries and potential locks

Large Log Files​

Query logging can generate large debug.log files quickly:

  1. Rotate logs regularly:

    mv wp-content/debug.log wp-content/debug.log.old
    touch wp-content/debug.log
    chmod 644 wp-content/debug.log

  2. Use log rotation tools (Linux/Mac):

    # /etc/logrotate.d/wordpress
    /var/www/html/wp-content/debug.log {
        weekly
        rotate 4
        compress
        missingok
        notifempty
    }

  3. Disable in production - Only enable query logging when actively debugging

Query Optimization Tips​

Apply Performance Indexes​

Run migration 011 to add critical performance indexes:

ALTER TABLE `rm_order` ADD INDEX `idx_dateadded` (`dateadded` DESC);
ALTER TABLE `rm_order` ADD INDEX `idx_ship_status` (`ship_status`);

See Database Migrations for details.

Verify Indexes Exist​

SHOW INDEX FROM rm_order;

Look for:

  • idx_dateadded - Speeds up ORDER BY dateadded DESC
  • idx_ship_status - Speeds up shipped filter
  • site - Speeds up site filter (should exist from baseline)

Monitor Slow Queries​

Queries over 0.100s should be investigated:

  1. Check if proper indexes exist
  2. Verify query execution plan with EXPLAIN
  3. Check for table locks or deadlocks
  4. Consider query optimization or caching

Expected Query Counts​

PageInitial LoadWith ModalNotes
Orders3 queries6 queriesSites + Orders (COUNT + SELECT)
Items3 queries-Sites + Items (COUNT + SELECT)
Order Details-+3 queriesOrder + Line Items + Addresses

Any significant deviation from these numbers indicates a problem.