orbit-db-profile

name: orbit-db-profile description: Database query profiling for a WordPress plugin — query count per page, slow queries (>100ms), N+1 detection, autoload bloat (wp_options autoload size), transient explosion, missing indexes, and cron-induced churn. Uses Query Monitor + MySQL performance_schema against a wp-env site. Use when the user says "DB profile", "query count", "N+1", "slow query", "autoload bloat", "Query Monitor", or after any feature that adds DB writes/reads.

🪐 orbit-db-profile — Query profiling

The DB layer most plugins ignore. 67 queries on the homepage isn't unusual — it's just preventable.

Quick start

# Default — profiles the homepage on wp-env port 8881
bash ~/Claude/orbit/scripts/db-profile.sh

# Custom URL list
WP_TEST_URL="http://localhost:8881" \
TEST_PAGES="/,/sample-page/,/wp-admin/admin.php?page=my-plugin" \
  bash scripts/db-profile.sh

Or via gauntlet (Step 8):

bash scripts/gauntlet.sh --plugin . --mode full

Output: reports/db-profile-<timestamp>.txt.

What it measures

Each is a release-blocker if exceeded.

How it works

1. Activate Query Monitor in wp-env
2. Visit each URL via headless Chrome (Playwright)
3. Read the QM panel output via WP-CLI
4. Parse + rank queries
5. Cross-reference against performance_schema.events_statements_summary_by_digest for slow-query stats

Common findings + fixes

N+1 queries

// BAD — 1 query for posts, then 1 query per post for meta
$posts = get_posts(['post_type' => 'product']);
foreach ($posts as $p) {
  $price = get_post_meta($p->ID, '_price', true);   // ← N+1
}

// GOOD — single batched meta query
$posts = get_posts([
  'post_type' => 'product',
  'fields' => 'ids',
]);
$prices = $wpdb->get_results(
  "SELECT post_id, meta_value FROM {$wpdb->postmeta} WHERE meta_key='_price' AND post_id IN (" .
  implode(',', array_map('intval', $posts)) . ")",
  OBJECT_K
);

Or use update_postmeta_cache($posts) + get_post_meta() — it primes the cache.

Autoload bloat

# Check what's autoloaded
wp-env run cli wp db query \
  "SELECT option_name, LENGTH(option_value) AS size
   FROM wp_options WHERE autoload='yes' ORDER BY size DESC LIMIT 20"

# Plugins commonly bloat this with: full settings JSON, log arrays, cache data

// BAD
add_option('my_plugin_huge_log', $bigArray);  // autoloaded by default

// GOOD
add_option('my_plugin_huge_log', $bigArray, '', 'no');  // explicit no-autoload
// Or use a transient for cache data
set_transient('my_plugin_cache', $cache, HOUR_IN_SECONDS);

Slow query (>100ms)

// BAD — full-table scan
$wpdb->get_results("SELECT * FROM {$wpdb->prefix}myplugin_log WHERE message LIKE '%error%'");

// GOOD — add an index
// In activation hook:
$wpdb->query("CREATE INDEX idx_message ON {$wpdb->prefix}myplugin_log (message(50))");

Cron storm

// BAD — schedules a task on every page load
add_action('init', function() {
  if (!wp_next_scheduled('my_task')) wp_schedule_event(time(), 'hourly', 'my_task');
});
// Logic looks idempotent but `wp_next_scheduled` itself queries the DB

// GOOD — schedule once, on activation
register_activation_hook(__FILE__, function() {
  if (!wp_next_scheduled('my_task')) wp_schedule_event(time(), 'hourly', 'my_task');
});

Transient flood

// BAD — write a transient per item
foreach ($items as $i) set_transient("my_plugin_item_$i->id", $i->data, HOUR_IN_SECONDS);

// GOOD — one transient with the whole array
set_transient('my_plugin_items', $items, HOUR_IN_SECONDS);

Read the report

[DB Profile] Page: /sample-page/

  Queries:    47   (target: < 30)        ⚠
  Slow:       1    (target: 0)           ❌
  Slowest:    240ms — SELECT * FROM wp_postmeta WHERE meta_key='_price'
  Autoload:   3.2MB                      ⚠
  N+1:        2 patterns detected        ❌
  Cron now:   0
  Transients: 4

❌ N+1 #1: 25 queries to wp_postmeta with consecutive post_ids
   → Source: includes/class-product-list.php:42
   → Fix: use update_postmeta_cache() or batched IN query

❌ Slow query: SELECT * FROM wp_postmeta WHERE meta_key='_price' (240ms)
   → 14,000 rows, no index on meta_key
   → Fix: add (post_id, meta_key) compound index — WP doesn't add this by default

Performance schema deep-dive

wp-env run cli wp db query \
  "SELECT digest_text, count_star, sum_timer_wait/1e9 AS sum_ms, avg_timer_wait/1e9 AS avg_ms
   FROM performance_schema.events_statements_summary_by_digest
   ORDER BY sum_timer_wait DESC LIMIT 20"

Shows: which query patterns ran most, how many times, total time spent. Identifies hot queries that aren't slow individually but eat budget cumulatively.

When to run

Pair with /orbit-wp-database

This skill measures runtime DB behaviour. /orbit-wp-database reviews code for DB anti-patterns. Run both — the measure tells you "you have N+1", the code review tells you "in this file at this line".