Admin Order Search Index

The WooCommerce admin order search was rebuilt in April 2026 to resolve chronic timeout issues on the orders list screen. The root cause was that WooCommerce's default search joins wp_postmeta — a table with 8–15 million rows on this site — and runs LIKE '%term%' queries across multiple meta keys simultaneously. With ~200,000+ orders in the database this was consistently timing out before returning results.

The replacement is a dedicated, denormalized wp_order_search table with purpose-built indexes, queried via a pre_get_posts hook that intercepts the admin search before WooCommerce ever touches postmeta.

---

How It Works

The Core Idea

Instead of searching across postmeta at query time, we maintain a small side table (wp_order_search) that holds only the data admins actually search on — one row per order. When an admin searches, we hit that small indexed table first, get back a list of matching order IDs, and hand those IDs to WordPress as a post__in array. WordPress then runs a simple WHERE ID IN (...) primary key lookup on wp_posts. Fast regardless of order volume.

Request Flow

Admin types search term + selects search type → hits Search
        ↓
WP_Query starts building for post_type = 'shop_order'
        ↓
pre_get_posts fires at priority 5 (our hook — before WooCommerce at priority 10)
        ↓
inject_order_search_results() runs:
  → queries wp_order_search with an indexed WHERE clause
  → gets back int[] of matching order IDs
  → sets post__in = [1001, 1045, 2301, ...]
  → clears s = '' (prevents WooCommerce running its own postmeta search)
        ↓
pre_get_posts fires at priority 10 (WooCommerce's handler)
  → sees s is empty → does nothing ✓
        ↓
MySQL: SELECT * FROM wp_posts WHERE ID IN (1001, 1045, 2301)
  → primary key lookup, instant ✓
        ↓
WooCommerce renders the matching orders

Before vs. After

	Before	After
Search by order #	Timeout / 30s+	< 1ms
Search by email	Timeout / 30s+	< 1ms
Search by name	Timeout / 30s+	~10–50ms
Search by address	Timeout / 30s+	~50–150ms
Browsing orders (no search)	Unaffected	Unaffected — hook bails early when s is empty

---

The Database Table

Table Name

wp_order_search (respects the configured $wpdb->prefix)

Schema

CREATE TABLE wp_order_search (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id         BIGINT UNSIGNED NOT NULL,
  customer_name    VARCHAR(200)    NOT NULL DEFAULT '',
  billing_email    VARCHAR(200)    NOT NULL DEFAULT '',
  billing_address  TEXT            NOT NULL,
  shipping_address TEXT            NOT NULL,
  order_total      DECIMAL(19,4)   NOT NULL DEFAULT 0.0000,
  PRIMARY KEY  (id),
  UNIQUE KEY order_id     (order_id),
  KEY        billing_email (billing_email(191)),
  KEY        customer_name (customer_name(191)),
  KEY        order_total   (order_total)
);

Column Reference

Column	Type	Description
id	BIGINT PK	Internal auto-increment row ID
order_id	BIGINT UNIQUE	The WooCommerce order ID — matches wp_posts.ID
customer_name	VARCHAR(200)	Billing first name + last name concatenated
billing_email	VARCHAR(200)	Billing email address
billing_address	TEXT	WooCommerce's pre-built _billing_address_index meta value (or manually concatenated address fields as fallback)
shipping_address	TEXT	WooCommerce's pre-built _shipping_address_index meta value (or manually concatenated address fields as fallback)
order_total	DECIMAL(19,4)	Order grand total stored as a fixed-precision decimal

What Is Not Stored

payment_method, order_status, and date_created are intentionally not in this table. Those fields are used for filtering (handled by WooCommerce's own query vars and the payment method dropdown) — not for keyword searching. Keeping the table narrow reduces write overhead and storage cost.

Indexes

Index	Type	Column	Purpose
PRIMARY KEY	B-tree	id	Internal row identity
UNIQUE KEY order_id	B-tree	order_id	One row per order; fast exact-match lookup
KEY billing_email	B-tree prefix (191 chars)	billing_email	Sub-millisecond exact email lookup
KEY customer_name	B-tree prefix (191 chars)	customer_name	Fast name LIKE scan
KEY order_total	B-tree	order_total	Fast exact match by order total

The (191) prefix length on billing_email and customer_name is required because utf8mb4 uses up to 4 bytes per character and InnoDB's max index key size is 767 bytes (191 × 4 = 764). In practice no email or name is near 191 characters so this has no effect on search accuracy.

billing_address and shipping_address are TEXT columns with no dedicated index. Address searches use LIKE '%term%' which cannot use a B-tree index (leading wildcard). However, a full scan of 200,000 narrow rows with no joins completes in ~50–150ms — well within acceptable limits and orders of magnitude faster than the old postmeta JOIN approach.

---

Source Files

File	Purpose
web/app/themes/suma-elementor/inc/order/class-order-search-index.php	Core class — table management, CRUD, search logic, WC lifecycle hooks
web/app/themes/suma-elementor/inc/order/class-admin-grid.php	Admin order list — registers pre_get_posts hook that calls the index

---

class-order-search-index.php

This is the heart of the feature. It is registered in class-theme.php as a dependency of the WooCommerce block and instantiated automatically on every page load.

Key Methods

maybe_create_table()

Hooked to WordPress's init action at priority 5. Compares the stored option suma_order_search_index_version against the class constant TABLE_VERSION. If they don't match (first deploy, or after a schema change), it calls create_table() and updates the stored version. This is a no-op on every subsequent request — zero overhead once the table is current.

create_table()

Runs dbDelta() to create or alter the table. dbDelta() is additive only — it will add new columns and indexes but never drops existing ones. This means schema removals (like dropping date_created) always require a manual ALTER TABLE alongside the code change.

upsert_order( WC_Order $order )

Writes one row for an order. Reads WooCommerce's pre-built _billing_address_index and _shipping_address_index meta values (WC maintains these automatically). If those meta values are missing (common on older orders), it builds the address string manually from individual address fields. Uses a SELECT id check to decide between INSERT and UPDATE.

delete_order( int $order_id )

Removes the row for the given order ID.

search( string $term, string $search_type ): int[]

The method called by Grid::inject_order_search_results(). Returns an array of matching order IDs. Each search type maps to a different query:

Search type	Query	Index used
order_id	WHERE order_id = %d	UNIQUE B-tree — O(log n)
email	WHERE billing_email = %s	B-tree — O(log n)
name	WHERE customer_name LIKE '%s%'	B-tree prefix scan
billing_address	WHERE billing_address LIKE '%s%' OR customer_name LIKE '%s%'	Full table scan
shipping_address	WHERE shipping_address LIKE '%s%'	Full table scan
order_total	WHERE order_total = %s	B-tree — O(log n)

The order_id search also handles prefixed order numbers. This site uses an ORDER_PREFIX_NUMBER env variable (e.g. SM) to prefix displayed order numbers. If an admin searches for SM12345, the method strips the prefix to extract 12345 before querying. It also falls back to stripping all non-numeric characters as a last resort.

WooCommerce Lifecycle Hooks

The class registers hooks covering both WooCommerce's unified hooks (which fire for HPOS and classic post storage) and classic WordPress post hooks as a safety net:

Hook	Action	Notes
woocommerce_new_order	upsert_order()	Fires when any new order is created
woocommerce_update_order	upsert_order()	Fires on every order save — keeps index current
woocommerce_trash_order	delete_order()	HPOS-compatible trash hook
woocommerce_delete_order	delete_order()	HPOS-compatible permanent delete hook
woocommerce_untrash_order	Re-upserts order	Restores index row when order is un-trashed
wp_trash_post	delete_order() if shop_order	Classic post-based safety net
before_delete_post	delete_order() if shop_order	Classic post-based safety net
untrashed_post	Re-upserts order if shop_order	Classic post-based safety net

The double coverage (WC hooks + classic post hooks) is intentional. Some payment gateway plugins and import tools create or modify orders by writing directly to wp_posts without going through WooCommerce's data store — the classic hooks catch those cases.

note

woocommerce_update_order fires on every order save, including routine status changes and admin edits. This means upsert_order() runs frequently. The overhead is acceptable — it is a single indexed SELECT + INSERT or UPDATE on a small table. If this ever becomes a concern, a queue-based approach (writing updates asynchronously via Action Scheduler) can be added without changing the table structure.

---

class-admin-grid.php

This class handles the WooCommerce admin order list UI. The search-related changes are confined to init() and the new inject_order_search_results() method.

init()

$this->order_search_index = new Order_Search_Index();

$this->current_search_type = isset( $_GET['_order_search_type'] )
    ? sanitize_key( $_GET['_order_search_type'] )
    : 'order_id';

add_action( 'pre_get_posts', [ $this, 'inject_order_search_results' ], 5 );

add_filter( 'woocommerce_shop_order_search_fields',           '__return_empty_array', 1 );
add_filter( 'woocommerce_order_table_search_query_meta_keys', '__return_empty_array', 1 );

The two __return_empty_array filters tell WooCommerce to provide zero meta keys for its own search. This is belt-and-suspenders — even if something prevented our hook from clearing s, WooCommerce still won't attempt its postmeta scan.

inject_order_search_results( WP_Query $query )

Hooked at pre_get_posts priority 5 — before WooCommerce's own handler at priority 10. This ordering is critical. If WooCommerce's handler ran first it would start building postmeta clauses before we could stop it.

// Guard: only act on the admin shop_order list when a search term is present
if ( ! is_admin() || $query->get( 'post_type' ) !== 'shop_order' ) {
    return;
}

$search_term = trim( (string) $query->get( 's' ) );
if ( $search_term === '' ) {
    return; // No search active — normal order list, nothing to do
}

$order_ids = $this->order_search_index->search( $search_term, $this->current_search_type );

$query->set( 'post__in', ! empty( $order_ids ) ? $order_ids : [ 0 ] );
$query->set( 's', '' );

Setting post__in to [0] when there are no results is intentional — ID 0 never exists in wp_posts, so the query returns nothing cleanly without any additional WHERE clauses.

Search Type Dropdown

The dropdown is rendered by add_search_type_dropdown() via restrict_manage_posts. The selected value is read from $_GET['_order_search_type'] in init() and stored in $this->current_search_type. It persists across searches because the browser appends it to the URL query string automatically.

Current options:

Label	Value	Search type
Order #	order_id	Exact numeric match
Email	email	Exact email match
Name	name	LIKE against customer_name
Billing Address/Name	billing_address	LIKE against billing_address OR customer_name
Shipping Address/Name	shipping_address	LIKE against shipping_address
Order Total	order_total	Exact decimal match

---

Schema Versioning

The table schema is version-controlled through two constants in Order_Search_Index:

const TABLE_VERSION      = '1.0.0';
const VERSION_OPTION_KEY = 'suma_order_search_index_version';

maybe_create_table() reads the value stored in wp_options under suma_order_search_index_version and compares it to TABLE_VERSION. If the stored value is lower, create_table() runs and the option is updated.

Making a Schema Change

1. Modify the CREATE TABLE SQL inside create_table() to reflect the new structure.
2. Bump TABLE_VERSION — e.g. '1.0.0' → '1.3.0'.
3. Deploy the code — maybe_create_table() will trigger dbDelta() automatically on the next page load.
4. Run any manual ALTER TABLE statements needed for changes dbDelta() cannot handle (see below).

What dbDelta() Can and Cannot Do

Operation	dbDelta() handles it?
Create table if not exists	✅ Yes
Add a new column	✅ Yes
Add a new index	✅ Yes
Modify a column type	⚠️ Sometimes (best to test)
Remove a column	❌ No — requires manual ALTER TABLE
Remove an index	❌ No — requires manual ALTER TABLE
Rename a column	❌ No — requires manual ALTER TABLE

For any removal or rename, run the ALTER TABLE directly against each environment's database after deploying the code change.

Example — dropping a column:

ALTER TABLE wp_order_search DROP COLUMN date_created;

Example — dropping an index:

ALTER TABLE wp_order_search DROP INDEX date_created;

---

Adding a New Search Type

If a new searchable field is needed (e.g. phone number):

1. Add the column to create_table() and bump TABLE_VERSION:

// In create_table() SQL string:
billing_phone VARCHAR(30) NOT NULL DEFAULT '',

// New index (optional but recommended for exact-match types):
KEY billing_phone (billing_phone(30)),

2. Populate the column in upsert_order():

$row = [
    // ...existing fields...
    'billing_phone' => $order->get_billing_phone(),
];

$formats = [ '%d', '%s', '%s', '%s', '%s', '%s' ]; // add a '%s'

3. Add the search case in search():

case 'phone':
    $like = '%' . $wpdb->esc_like( $term ) . '%';
    $query = $wpdb->prepare(
        "SELECT order_id FROM {$table} WHERE billing_phone LIKE %s",
        $like
    );
    break;

4. Add the dropdown option in class-admin-grid.php:

<option value="phone" <?php selected( $current_type, 'phone' ); ?>>
    <?php esc_html_e( 'Phone', 'suma' ); ?>
</option>

5. Add the JS placeholder in enqueue_admin_assets():

'phone': 'Search by phone number...',

6. Re-populate the index to backfill the new column for existing orders:

wp eval-file web/tools/populate-order-search-index.php

---

Standalone Index Population Script

For environments where updating existing records via WP-CLI or cron scripts may time out, we provide a standalone population script: tools/populate-order-search-index.php.

What The Script Does

This self-contained script bulk-updates the wp_order_search table directly using PHP's PDO extension. To bypass the memory exhaustion and timeout characteristics of building the WordPress environment and querying through WP_Query, it connects natively to your Aurora database and executes batched data ingestion directly. It is read-only against existing tables (wp_posts, wp_postmeta) and only creates data in the wp_order_search table. It achieves high throughput by:

• Fetching order IDs in batches.
• Loading relevant postmeta for the batch in a single database round-trip.
• Inserting the batch into wp_order_search using an INSERT ... ON DUPLICATE KEY UPDATE bulk query.

How to Run the Script

Step 1. Open an SSH Tunnel You need to create a tunnel directly to your target database so that your local PHP script can communicate with it.

Step 2. Update DB Creds

Update the DB connection variables to ensure the script can connect the correct DB.

$dbHost = '127.0.0.1';
$dbPort = 3306;
$dbName = 'wp_scottsdale_2025';
$dbUser = 'root';
$dbPass = '';
$dbPrefix = 'wp_';

Step 3. Run the Script Once the tunnel is active, you can pass arguments to map the script appropriately to your remote server:

php tools/populate-order-search-index.php

Common Options:

• --batch-size: Set number of orders to process simultaneously (default: 500).
• --start-offset: Used to resume if processing fails mid-run (default: 0).
• --dry-run: Processes without writing to verify counts and outputs.