Orders Page Query Analysis

Complete breakdown of all database queries executed when loading the Orders admin page.

Query Overview

When you navigate to /wp-admin/admin.php?page=gsm-orders, the React application makes REST API calls that execute these database queries.

Total Query Count

Scenario	Queries	Endpoints
Initial page load	3	Sites + Orders
With order details modal	6	Sites + Orders + Order Details
Page navigation	2	Orders only (sites cached)

Detailed Query Breakdown

1. Sites Endpoint (Deferred 200ms)

API Call: GET /wp-json/gsm-middleware/v1/sites

Purpose: Populate the site filter dropdown

Query:

SELECT * FROM rm_sites 
WHERE is_active = 1 
ORDER BY name ASC

Performance:

• Execution time: 0.001-0.005 seconds
• Result rows: 10-50 (typical)
• HTTP caching: 5 minutes
• Deferred: Loads 200ms after main content

Optimization:

• Cached via HTTP Cache-Control header
• Subsequent requests use browser cache
• No indexes needed (small result set)

---

2. Orders Endpoint - COUNT Query

API Call: GET /wp-json/gsm-middleware/v1/orders?page=1&per_page=20

Purpose: Calculate total pages for pagination

Query:

SELECT COUNT(*) 
FROM `rm_order` 
WHERE 1=1

With Filters Applied:

SELECT COUNT(*) 
FROM `rm_order` 
WHERE 1=1 
  AND site = 1                    -- Site filter
  AND imported = 1                -- Status filter
  AND stripe = '1'                -- Payment method filter
  AND ship_status = 1             -- Shipped filter
  AND (                           -- Search filter
    number LIKE '%term%' OR
    nav_sales_order_number LIKE '%term%' OR
    trans_id LIKE '%term%'
  )

Performance:

Database State	Time	Notes
Without indexes	0.050-0.500s	Full table scan
With idx_ship_status	0.010-0.050s	Partial improvement
With all filters indexed	0.001-0.010s	Optimal

Required Indexes:

• idx_ship_status (Migration 011)
• site index (baseline schema)
• rm_order_imported_IDX (baseline schema)

---

3. Orders Endpoint - SELECT Query

API Call: Same as COUNT query above

Purpose: Fetch paginated order rows with site names

Query:

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

Columns Returned:

• All rm_order columns (id, site, number, trans_id, etc.)
• site_name from joined rm_sites table

Performance:

Database State	Time	Notes
Without indexes	0.100-1.000s	Full table scan + filesort
With idx_dateadded	0.005-0.050s	Index used for ORDER BY
100K+ rows, no index	2.000-10.000s	Extremely slow

Required Indexes:

• idx_dateadded DESC (Migration 011) - Critical for performance
• site index for JOIN optimization

Pagination Examples:

-- Page 1
LIMIT 20 OFFSET 0

-- Page 2
LIMIT 20 OFFSET 20

-- Page 5
LIMIT 20 OFFSET 80

---

Filter Query Examples

Filter by Site

SELECT o.*, s.name as site_name 
FROM `rm_order` o 
LEFT JOIN `rm_sites` s ON o.site = s.id 
WHERE 1=1 AND site = 1
ORDER BY o.dateadded DESC 
LIMIT 20 OFFSET 0

Filter by Import Status

WHERE 1=1 
  AND imported = 1  -- Imported orders
  -- OR
  AND imported = 0  -- Pending orders

Filter by Payment Method

Payment methods use flag columns:

WHERE 1=1 
  AND stripe = '1'   -- Stripe payments
  -- OR
  AND klarna = '1'   -- Klarna payments
  -- OR
  AND paypal = '1'   -- PayPal payments
  -- OR
  AND credova = '1'  -- Credova payments
  -- OR
  AND sezzle = '1'   -- Sezzle payments
  -- OR
  AND nmi = '1'      -- NMI payments

Filter by Shipped Status

WHERE 1=1 
  AND ship_status = 1  -- Shipped orders
  -- OR
  AND ship_status = 0  -- Not shipped

Index: idx_ship_status (Migration 011)

Search Orders

WHERE 1=1 
  AND (
    number LIKE '%12345%' OR
    nav_sales_order_number LIKE '%12345%' OR
    trans_id LIKE '%12345%'
  )

Performance Note: LIKE with leading wildcard (%term%) cannot use indexes efficiently. Consider full-text search for large datasets.

Combined Filters Example

SELECT o.*, s.name as site_name 
FROM `rm_order` o 
LEFT JOIN `rm_sites` s ON o.site = s.id 
WHERE 1=1 
  AND site = 3
  AND imported = 1
  AND stripe = '1'
  AND ship_status = 1
  AND number LIKE '%ORD123%'
ORDER BY o.dateadded DESC 
LIMIT 20 OFFSET 0

---

Order Details Modal Queries

When clicking "View" on an order, these additional queries execute:

Query 1: Order with Site Name

API Call: GET /wp-json/gsm-middleware/v1/orders/{id}

SELECT o.*, s.name as site_name 
FROM `rm_order` o 
LEFT JOIN `rm_sites` s ON o.site = s.id 
WHERE o.id = 12345

Performance: 0.001-0.005s (primary key lookup)

---

Query 2: Line Items with SKUs

SELECT li.*, i.sku 
FROM `rm_lineitems` li 
LEFT JOIN `rm_items` i ON li.item = i.id 
WHERE li.`order` = 12345 
ORDER BY li.line ASC

Returns:

• Line item ID, quantity, price
• Product SKU from rm_items table
• Sorted by line number

Performance: 0.002-0.010s (indexed foreign key)

---

Query 3: Addresses

SELECT * 
FROM `rm_address` 
WHERE `order` = 12345

Returns:

• Billing address (type = 1)
• Shipping address (type = 0)

Performance: 0.001-0.003s (indexed foreign key)

---

Query Performance Matrix

Without Indexes (Large Tables)

Query	Rows Scanned	Time	Impact
COUNT	500K	0.150s	Medium
SELECT	500K	0.800s	High
ORDER BY	500K	1.200s	Critical
Total	-	~2.150s	Unacceptable

With Migration 011 Indexes

Query	Rows Scanned	Time	Impact
COUNT	500K	0.008s	None
SELECT	20	0.012s	None
ORDER BY	Index	0.005s	None
Total	-	~0.025s	Excellent

Improvement: ~86x faster with proper indexes

---

Index Strategy

Critical Indexes (Migration 011)

-- For ORDER BY dateadded DESC
ALTER TABLE `rm_order` 
ADD INDEX `idx_dateadded` (`dateadded` DESC);

-- For ship_status filter
ALTER TABLE `rm_order` 
ADD INDEX `idx_ship_status` (`ship_status`);

Existing Indexes (Baseline Schema)

-- Primary key (order lookups)
PRIMARY KEY (`id`, `site`)

-- Site filter
KEY `site` (`site`)

-- Import status filter
KEY `rm_order_imported_IDX` (`imported`, `verified`, `fully_import_from_website`)

-- Order number lookup
KEY `rm_order_number_IDX` (`number`)

Composite Index Considerations

For queries with multiple filters, consider composite indexes:

-- If frequently filtering by site + ship_status
ALTER TABLE `rm_order` 
ADD INDEX `idx_site_shipped` (`site`, `ship_status`, `dateadded` DESC);

Trade-off: More indexes = faster reads, slower writes. Only add if query patterns justify it.

---

Query Optimization Checklist

• ✅ Apply Migration 011 - Adds critical idx_dateadded and idx_ship_status
• ✅ Verify indexes exist - Run SHOW INDEX FROM rm_order
• ✅ Monitor query times - Enable query logging (see Query Logging)
• ✅ Check execution plans - Use EXPLAIN for slow queries
• ✅ Reduce page size - Already optimized to 20 items (was 50)
• ✅ Cache sites data - Already cached for 5 minutes
• ✅ Defer non-critical - Sites dropdown loads 200ms after orders

---

Troubleshooting Slow Queries

Symptoms

• Page takes >2 seconds to load
• Browser shows "Loading orders..." for extended time
• debug.log shows queries over 0.100s

Diagnosis Steps

1. Enable query logging:

   define( 'GSM_LOG_QUERIES', true );
   define( 'SAVEQUERIES', true );

2. Check for missing indexes:

   SHOW INDEX FROM rm_order;

   Should see: idx_dateadded, idx_ship_status

3. Analyze query execution:

   EXPLAIN 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;

4. Check table size:

   SELECT COUNT(*) FROM rm_order;
   SELECT COUNT(*) FROM rm_sites;

Solutions

Issue	Solution
No indexes	Apply migration 011
Indexes exist but not used	Check column data types, update statistics
Table locks	Check for long-running operations
>1M orders	Consider archiving old orders
Network latency	Enable browser caching, use CDN

---

Related Documentation

• Query Logging - Enable debug logging
• Performance Optimization - Speed improvements
• Database Migrations - Apply updates
• Database Schema - Table structure