HPOS-Safe WooCommerce Order Queries

Compatibility target: HPOS enabled with compatibility mode disabled
API baseline: WooCommerce 8.2 or newer for advanced HPOS query clauses

An order query can appear to work correctly in development while still being fundamentally incompatible with High-Performance Order Storage.

This usually happens because the development store has WooCommerce’s compatibility synchronization enabled. Orders written to the HPOS tables are mirrored into wp_posts and wp_postmeta, allowing old queries to continue returning apparently correct results.

Turn synchronization off, create a new order, and the same code may return nothing.

Starting with WooCommerce 10.7, HPOS synchronization on read is also disabled by default. Direct changes made to legacy post tables are no longer expected to be silently pulled back into HPOS when an order is read. WooCommerce recommends moving extensions to its CRUD and query APIs rather than relying on that transitional behavior. (The WooCommerce Developer Blog)

The rule for extension developers is straightforward:

Query the order’s WooCommerce properties, not the database tables that happened to store those properties in the past.

For individual orders, use wc_get_order(). For collections, use wc_get_orders() or WC_Order_Query. For values, use WC_Order getters and setters. For administration screens, use WooCommerce’s order-list hooks instead of assuming that orders are WordPress posts.

---

Why direct wp_posts and wp_postmeta queries fail

Before HPOS, WooCommerce stored orders as WordPress posts:

wp_posts
└── post_type = shop_order

wp_postmeta
├── _customer_user
├── _billing_email
├── _order_currency
├── _order_total
├── _payment_method
└── extension-specific metadata

That architecture encouraged code such as:

$orders = get_posts(
	array(
		'post_type'      => 'shop_order',
		'post_status'    => 'wc-processing',
		'posts_per_page' => 50,
		'meta_query'     => array(
			array(
				'key'   => '_payment_method',
				'value' => 'stripe',
			),
		),
	)
);

Or direct SQL:

global $wpdb;

$order_ids = $wpdb->get_col(
	$wpdb->prepare(
		"
		SELECT posts.ID
		FROM {$wpdb->posts} AS posts
		INNER JOIN {$wpdb->postmeta} AS payment_meta
			ON payment_meta.post_id = posts.ID
		WHERE posts.post_type = 'shop_order'
			AND posts.post_status = 'wc-processing'
			AND payment_meta.meta_key = '_payment_method'
			AND payment_meta.meta_value = %s
		",
		'stripe'
	)
);

Neither approach is HPOS-safe.

HPOS uses dedicated order tables

When HPOS is authoritative, order data is primarily stored in dedicated WooCommerce tables:

wc_orders
wc_order_addresses
wc_order_operational_data
wc_orders_meta

The actual table names include the site’s database prefix. Many values that once appeared to be arbitrary post metadata—such as the customer ID, billing email, currency, total, payment method, creation date, and parent order ID—are first-class properties or columns under HPOS. (The WooCommerce Developer Blog)

The old physical key therefore should not define your query.

For example:

_customer_user

represents the semantic property:

customer_id

Likewise:

_order_total

represents:

total

Query the property instead of reconstructing the old storage implementation.

Compatibility mode can hide defects

During a migration, WooCommerce can keep the HPOS and posts-based datastores synchronized. One store is authoritative, while the other acts as backup storage.

That does not make direct post queries compatible. It only allows them to continue seeing a synchronized copy.

Once compatibility mode is disabled:

• Newly created HPOS orders may not have usable legacy post data.
• Existing post data can become stale.
• Direct writes to post meta may update a value WooCommerce no longer reads.
• Queries against shop_order posts can omit current orders.
• Administration hooks tied to WordPress post screens may stop running.

WooCommerce’s HPOS migration guidance explicitly recommends replacing direct post and post-meta access with WooCommerce CRUD APIs. (The WooCommerce Developer Blog)

A post row does not prove post storage is usable

WooCommerce can retain lightweight placeholder posts to reserve order IDs after legacy order data is removed. These use a placeholder post type rather than restoring the old order-storage contract.

Therefore, code like this is not a valid HPOS test:

if ( get_post( $order_id ) ) {
	// Assume the order can be handled through post APIs.
}

The supported check is:

$order = wc_get_order( $order_id );

if ( $order instanceof WC_Order ) {
	// Work with the order through WooCommerce.
}

WooCommerce’s HPOS cleanup process can remove legacy order metadata while retaining placeholder rows, which is why the presence of a post ID should not be treated as evidence that post APIs contain authoritative order data. (The WooCommerce Developer Blog)

---

Use wc_get_orders() as the storage-independent query API

WooCommerce documents wc_get_orders() and WC_Order_Query as the standard, future-compatible ways to retrieve orders. Direct WP_Query, get_posts(), and custom SQL against internal order tables are discouraged because they couple the extension to a particular datastore. (The WooCommerce Developer Blog)

A normal query looks like this:

$cutoff = (
	new DateTimeImmutable(
		'-30 days',
		new DateTimeZone( 'UTC' )
	)
)->getTimestamp();

$orders = wc_get_orders(
	array(
		'type'           => 'shop_order',
		'status'         => array(
			'wc-processing',
			'wc-completed',
		),
		'payment_method' => 'stripe',
		'date_created'   => '>=' . $cutoff,
		'limit'          => 50,
		'orderby'        => 'date',
		'order'          => 'DESC',
		'return'         => 'objects',
	)
);

foreach ( $orders as $order ) {
	if ( ! $order instanceof WC_Order ) {
		continue;
	}

	printf(
		"Order #%d: %s %s\n",
		$order->get_id(),
		$order->get_currency(),
		$order->get_total()
	);
}

The same call works whether the active data store is HPOS or the legacy posts implementation, provided all supplied arguments are supported by both.

wc_get_orders() is largely a convenient wrapper around WC_Order_Query:

$query = new WC_Order_Query(
	array(
		'type'   => 'shop_order',
		'status' => 'wc-processing',
		'limit'  => 25,
	)
);

$query->set( 'billing_country', 'IQ' );
$query->set( 'orderby', 'date' );
$query->set( 'order', 'DESC' );

$orders = $query->get_orders();

Use WC_Order_Query when query arguments are assembled incrementally or when exposing a reusable query object is useful. For most extension code, wc_get_orders() is simpler.

---

Map legacy query intent to WooCommerce arguments

Do not mechanically translate SQL syntax. Identify what the code is trying to find and use the corresponding order property.

Legacy assumption or query	HPOS-safe WooCommerce argument
post_type = shop_order	type => 'shop_order'
post_status	status
posts_per_page	limit
paged	paged
offset	offset
fields => 'ids'	return => 'ids'
post_parent	parent
post__not_in	exclude
_customer_user	customer_id
_billing_email	billing_email or customer
_billing_first_name	billing_first_name
_billing_last_name	billing_last_name
_billing_country	billing_country
_shipping_country	shipping_country
_order_currency	currency
_payment_method	payment_method
_created_via	created_via
_order_total	total for an exact-value query
_date_paid	date_paid
_date_completed	date_completed
post_date	date_created
post_modified	date_modified
Arbitrary extension meta	HPOS meta_query or a datastore adapter
Numeric comparison on an order property	HPOS field_query

WooCommerce exposes documented arguments for order type, status, parent, exclusion, return format, customer data, billing and shipping data, payment data, amounts, and order dates. Those arguments are preferable to querying the historical meta key that once stored the value. (The WooCommerce Developer Blog)

Prefer first-class arguments

This is preferable:

$order_ids = wc_get_orders(
	array(
		'customer_id'    => 42,
		'currency'       => 'USD',
		'payment_method' => 'stripe',
		'billing_country' => 'US',
		'limit'          => 100,
		'return'         => 'ids',
	)
);

Avoid expressing the same query as metadata:

// Do not use this for core order properties.
$order_ids = wc_get_orders(
	array(
		'meta_query' => array(
			array(
				'key'   => '_customer_user',
				'value' => 42,
			),
			array(
				'key'   => '_order_currency',
				'value' => 'USD',
			),
			array(
				'key'   => '_payment_method',
				'value' => 'stripe',
			),
		),
	)
);

The first query describes business properties. The second describes a legacy storage layout and also depends on an advanced query feature that is only available while HPOS is active.

---

Pagination without loading the entire order table

A common large-store defect is an unbounded query:

$orders = wc_get_orders(
	array(
		'limit' => -1,
	)
);

This can instantiate every matching order during one PHP request. It can exhaust memory, exceed execution limits, overload object caches, and make a scheduled task impossible to resume safely.

Use bounded pages instead.

Paginating an administration or reporting screen

Set paginate to true when the interface needs the total result count and maximum number of pages:

$page = isset( $_GET['paged'] )
	? max( 1, absint( wp_unslash( $_GET['paged'] ) ) )
	: 1;

$result = wc_get_orders(
	array(
		'type'     => 'shop_order',
		'status'   => array(
			'wc-processing',
			'wc-completed',
		),
		'limit'    => 50,
		'paged'    => $page,
		'paginate' => true,
		'orderby'  => 'date',
		'order'    => 'DESC',
		'return'   => 'ids',
	)
);

foreach ( $result->orders as $order_id ) {
	printf(
		'<a href="%s">#%d</a><br>',
		esc_url( wc_get_endpoint_url( 'view-order', $order_id, wc_get_page_permalink( 'myaccount' ) ) ),
		absint( $order_id )
	);
}

printf(
	'<p>%d matching orders across %d pages.</p>',
	absint( $result->total ),
	absint( $result->max_num_pages )
);

With pagination enabled, WooCommerce returns an object containing:

orders
total
max_num_pages

The offset argument can also be used, but it overrides normal page-based behavior. Use either paged or offset intentionally rather than combining them accidentally. (The WooCommerce Developer Blog)

Paginating background work

Background jobs often do not need a total count. They only need the next bounded batch.

$batch_size = 250;
$page       = 1;

$window_start = (
	new DateTimeImmutable(
		'2026-07-01 00:00:00',
		new DateTimeZone( 'UTC' )
	)
)->getTimestamp();

$window_end = (
	new DateTimeImmutable(
		'2026-07-31 23:59:59',
		new DateTimeZone( 'UTC' )
	)
)->getTimestamp();

$date_window = sprintf(
	'%d...%d',
	$window_start,
	$window_end
);

do {
	$order_ids = wc_get_orders(
		array(
			'type'         => 'shop_order',
			'date_created' => $date_window,
			'limit'        => $batch_size,
			'paged'        => $page,
			'orderby'      => 'ID',
			'order'        => 'ASC',
			'return'       => 'ids',
		)
	);

	foreach ( $order_ids as $order_id ) {
		// Queue or process one order idempotently.
		hxf_queue_order_export( absint( $order_id ) );
	}

	++$page;
} while ( count( $order_ids ) === $batch_size );

For a very large export, partition the work into day, week, or month windows so that each query remains narrowly scoped. Store checkpoints so a failed job can resume without starting over.

Also exclude new orders from a running export by fixing the upper creation-date boundary when the job starts. Otherwise, newly created orders can change later pages while the export is still running.

---

Date queries and timezone behavior

WooCommerce supports four portable top-level date arguments:

date_created
date_modified
date_completed
date_paid

These accept exact dates, comparisons, ranges, and Unix timestamps. Date strings are interpreted in the site’s timezone, while Unix timestamps are interpreted as UTC. (The WooCommerce Developer Blog)

Query an exact calendar period

$order_ids = wc_get_orders(
	array(
		'date_created' => '2026-07-01...2026-07-31',
		'limit'        => 100,
		'return'       => 'ids',
	)
);

This asks for orders created during July 2026 according to the site timezone.

Query from a UTC timestamp

$paid_after = (
	new DateTimeImmutable(
		'2026-07-01 00:00:00',
		new DateTimeZone( 'UTC' )
	)
)->getTimestamp();

$order_ids = wc_get_orders(
	array(
		'date_paid' => '>=' . $paid_after,
		'limit'     => 100,
		'return'    => 'ids',
	)
);

Supported comparison patterns

wc_get_orders(
	array(
		'date_created' => '2026-07-15',
	)
);

wc_get_orders(
	array(
		'date_created' => '>=2026-07-01',
	)
);

wc_get_orders(
	array(
		'date_created' => '<2026-08-01',
	)
);

wc_get_orders(
	array(
		'date_created' => '2026-07-01...2026-07-31',
	)
);

Use top-level date arguments whenever they can express the requirement. They are easier to understand and can work across both HPOS and legacy order storage.

---

Advanced date_query clauses are HPOS-only

HPOS supports a more expressive date_query syntax inspired by WordPress’s WP_Date_Query.

It can combine date conditions and inspect components such as year, month, day, weekday, and hour:

use Automattic\WooCommerce\Utilities\OrderUtil;

if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
	$order_ids = wc_get_orders(
		array(
			'date_query' => array(
				'relation' => 'AND',

				array(
					'column' => 'date_paid_gmt',
					'after'  => '1 month ago',
				),

				array(
					'column'  => 'date_created_gmt',
					'hour'    => 12,
					'compare' => '<',
				),
			),
			'limit'      => 100,
			'return'     => 'ids',
		)
	);
}

Advanced date_query, field_query, and meta_query support in wc_get_orders() is available only when HPOS is the active datastore. Extensions that still support the legacy posts datastore must either use portable top-level arguments or provide a separate legacy query implementation. (The WooCommerce Developer Blog)

Do not silently return an empty result when an HPOS-only query is requested on legacy storage. Either:

• Fall back to an equivalent portable query.
• Use a deliberately isolated legacy adapter.
• Disable that report with a clear requirement message.
• Declare the extension HPOS-only when that is an intentional product decision.

---

Reading and writing order metadata safely

HPOS does not prohibit custom order metadata.

It changes how that metadata must be accessed.

Read and write through WC_Order

$order = wc_get_order( $order_id );

if ( ! $order instanceof WC_Order ) {
	return;
}

$order->update_meta_data(
	'_hxf_export_batch',
	'2026-07'
);

$order->update_meta_data(
	'_hxf_risk_score',
	92
);

$order->save();

Read values through the same object:

$order = wc_get_order( $order_id );

if ( ! $order instanceof WC_Order ) {
	return;
}

$batch = $order->get_meta(
	'_hxf_export_batch',
	true
);

$risk_score = $order->get_meta(
	'_hxf_risk_score',
	true
);

Avoid:

update_post_meta(
	$order_id,
	'_hxf_export_batch',
	'2026-07'
);

$batch = get_post_meta(
	$order_id,
	'_hxf_export_batch',
	true
);

Direct post-meta functions operate on the legacy datastore. Under HPOS, WooCommerce may not read those values, and compatibility synchronization should not be treated as a repair mechanism. (The WooCommerce Developer Blog)

---

Querying custom metadata under HPOS

For HPOS-only metadata searches, use meta_query:

use Automattic\WooCommerce\Utilities\OrderUtil;

if ( ! OrderUtil::custom_orders_table_usage_is_enabled() ) {
	throw new RuntimeException(
		'This report requires WooCommerce HPOS.'
	);
}

$order_ids = wc_get_orders(
	array(
		'type'       => 'shop_order',
		'status'     => array(
			'wc-processing',
			'wc-completed',
		),
		'meta_query' => array(
			'relation' => 'AND',

			array(
				'key'     => '_hxf_export_batch',
				'value'   => '2026-07',
				'compare' => '=',
			),

			array(
				'key'     => '_hxf_risk_score',
				'value'   => 80,
				'compare' => '>=',
				'type'    => 'NUMERIC',
			),
		),
		'limit'      => 100,
		'return'     => 'ids',
	)
);

HPOS metadata queries support nested relations, comparison operators, and type casting in a form similar to WP_Meta_Query. The important limitation is that this wc_get_orders() feature is HPOS-only. (The WooCommerce Developer Blog)

Do not use custom meta for core properties

This is incorrect:

'meta_query' => array(
	array(
		'key'     => '_order_total',
		'value'   => 100,
		'compare' => '>=',
		'type'    => 'NUMERIC',
	),
)

total is an order property. For an exact value, use:

'total' => 100

For an advanced numeric comparison under HPOS, use field_query:

$order_ids = wc_get_orders(
	array(
		'field_query' => array(
			array(
				'field'   => 'total',
				'value'   => 100,
				'compare' => '>=',
				'type'    => 'NUMERIC',
			),
		),
		'limit'       => 100,
		'return'      => 'ids',
	)
);

You can also combine first-class fields:

$order_ids = wc_get_orders(
	array(
		'field_query' => array(
			'relation' => 'OR',

			array(
				'field'   => 'total',
				'value'   => 100,
				'compare' => '>=',
				'type'    => 'NUMERIC',
			),

			array(
				'field'   => 'shipping_total',
				'value'   => 25,
				'compare' => '>=',
				'type'    => 'NUMERIC',
			),
		),
		'limit'       => 100,
		'return'      => 'ids',
	)
);

Use a top-level property argument for simple equality. Reserve field_query for comparisons and grouped conditions that cannot be expressed otherwise. field_query is also HPOS-only. (The WooCommerce Developer Blog)

---

Supporting custom metadata queries across both datastores

An extension that supports both HPOS and legacy storage may need to expose one internal query argument while translating it differently for each datastore.

The calling code should remain storage-independent:

$order_ids = wc_get_orders(
	array(
		'hxf_export_batch' => '2026-07',
		'limit'            => 100,
		'return'           => 'ids',
	)
);

Then isolate the storage-specific translation.

Register the correct adapter

use Automattic\WooCommerce\Utilities\OrderUtil;

add_action(
	'woocommerce_init',
	static function (): void {
		if ( OrderUtil::custom_orders_table_usage_is_enabled() ) {
			add_filter(
				'woocommerce_order_query_args',
				'hxf_map_export_batch_for_hpos'
			);

			return;
		}

		add_filter(
			'woocommerce_order_data_store_cpt_get_orders_query',
			'hxf_map_export_batch_for_legacy_storage',
			10,
			2
		);
	}
);

HPOS adapter

function hxf_map_export_batch_for_hpos(
	array $query_args
): array {
	if (
		! array_key_exists(
			'hxf_export_batch',
			$query_args
		)
	) {
		return $query_args;
	}

	$value = wc_clean(
		(string) $query_args['hxf_export_batch']
	);

	unset( $query_args['hxf_export_batch'] );

	if (
		! isset( $query_args['meta_query'] )
		|| ! is_array( $query_args['meta_query'] )
	) {
		$query_args['meta_query'] = array();
	}

	$query_args['meta_query'][] = array(
		'key'     => '_hxf_export_batch',
		'value'   => $value,
		'compare' => '=',
	);

	return $query_args;
}

Legacy posts adapter

function hxf_map_export_batch_for_legacy_storage(
	array $wp_query_args,
	array $query_vars
): array {
	if (
		! array_key_exists(
			'hxf_export_batch',
			$query_vars
		)
	) {
		return $wp_query_args;
	}

	$value = wc_clean(
		(string) $query_vars['hxf_export_batch']
	);

	if (
		! isset( $wp_query_args['meta_query'] )
		|| ! is_array( $wp_query_args['meta_query'] )
	) {
		$wp_query_args['meta_query'] = array();
	}

	$wp_query_args['meta_query'][] = array(
		'key'     => '_hxf_export_batch',
		'value'   => $value,
		'compare' => '=',
	);

	return $wp_query_args;
}

WooCommerce documents woocommerce_order_query_args for modifying HPOS order query arguments and woocommerce_order_data_store_cpt_get_orders_query for translating custom variables under the legacy CPT data store. (The WooCommerce Developer Blog)

This architecture keeps datastore branching at the edge:

Business requirement
        │
        ▼
hxf_export_batch query argument
        │
        ├── HPOS adapter
        └── Legacy adapter

Avoid scattering HPOS checks throughout controllers, reports, REST endpoints, and administration screens.

---

Large-store order-query performance

HPOS improves the storage model, but it does not make every possible query inexpensive.

WooCommerce has published benchmarks showing substantial improvements for some query patterns, including an approximately tenfold metadata-query improvement on a test dataset containing roughly 400,000 orders. That result demonstrates HPOS’s potential; it is not a guarantee that every query on every store will be ten times faster. Data shape, cache state, query selectivity, hardware, indexes, and extension behavior all matter. (The WooCommerce Developer Blog)

Common performance mistakes

Anti-pattern	Better approach
limit => -1 in a web request	Process bounded batches
Returning full objects only to collect IDs	Use return => 'ids'
Fetching every status and filtering in PHP	Include status in the query
Loading every year and filtering by date later	Include a narrow date range
Broad %value% metadata searches	Store normalized, queryable values
Sorting thousands of orders by custom text meta	Use an extension-owned lookup table
paginate => true in a job that needs no total	Request only the next batch
Deep, unbounded page traversal	Partition by date or another stable boundary
One external API request per list-table row	Store or batch the external status
A global query filter for one admin screen	Use the list-table-specific hook
Repeatedly loading the same order	Pass the existing WC_Order object
Running a full export synchronously	Queue resumable background batches

Constrain the database query first

This is poor:

$orders = wc_get_orders(
	array(
		'limit' => 1000,
	)
);

$matching = array_filter(
	$orders,
	static function ( WC_Order $order ): bool {
		return (
			$order->has_status( 'completed' )
			&& 'USD' === $order->get_currency()
			&& $order->get_total() >= 100
		);
	}
);

Move supported conditions into the database query:

$query_args = array(
	'status'   => 'wc-completed',
	'currency' => 'USD',
	'limit'    => 100,
	'return'   => 'ids',
);

if (
	Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled()
) {
	$query_args['field_query'] = array(
		array(
			'field'   => 'total',
			'value'   => 100,
			'compare' => '>=',
			'type'    => 'NUMERIC',
		),
	);
}

$order_ids = wc_get_orders( $query_args );

When legacy support is required, decide whether the range condition can be implemented through a separate adapter or whether the feature should require HPOS.

Return IDs when IDs are enough

For queueing, deletion candidates, or synchronization jobs:

$order_ids = wc_get_orders(
	array(
		'status' => 'wc-processing',
		'limit'  => 200,
		'return' => 'ids',
	)
);

For a page that immediately needs totals, addresses, and metadata, returning objects may be more convenient:

$orders = wc_get_orders(
	array(
		'status' => 'wc-processing',
		'limit'  => 50,
		'return' => 'objects',
	)
);

Choose based on what the caller actually consumes.

Keep work resumable

A large-store process should be able to answer:

What was the last completed batch?
Can this order safely be processed twice?
Can the job resume after a timeout?
Are new orders excluded from the current snapshot?
Can one failed order be retried independently?

The query and processing callback should both be idempotent. A retry should not create duplicate exports, duplicate remote orders, repeated emails, or duplicate metadata rows.

---

Index considerations

Indexes should follow actual query patterns, not guesses.

MySQL can use indexes to avoid scanning every row, support selective lookups, and sometimes satisfy ordering. With a composite index, the leftmost-prefix rule matters: an index on (batch_key, state, order_id) can support queries beginning with batch_key, but it generally does not serve a query on state alone in the same way. (MySQL Developer Zone)

At the same time, every additional index consumes storage and adds work to inserts, updates, and deletes. An index that accelerates one report can slow the order-write path used by every checkout. (MySQL Developer Zone)

Do not casually modify WooCommerce-owned tables

Avoid shipping a plugin that blindly adds indexes to:

wc_orders
wc_order_addresses
wc_order_operational_data
wc_orders_meta

WooCommerce owns those schemas and their migrations. A core update may alter the table, index, or query plan. An extension-defined index may also create significant write amplification on stores where the associated report is rarely used.

Start by asking:

1. Can this query use a documented first-class argument?
2. Can the result be computed asynchronously and cached?
3. Is the custom value queried frequently enough to justify a lookup table?
4. Does the store actually have a measured performance problem?
5. Has the query plan been inspected against production-like data?

Choose storage based on workload

Data requirement	Suitable storage
Core order field	Existing WC_Order property
Custom value read for one known order	Order metadata
Occasional small metadata report	HPOS meta_query
Frequent filter or sort across millions of orders	Extension-owned lookup table
Aggregated dashboard metrics	Precomputed reporting table
Full-text analytics	External search or analytics system

Example extension-owned lookup table

Suppose an export integration frequently queries orders by batch and state:

CREATE TABLE wp_hxf_order_export_lookup (
	order_id BIGINT UNSIGNED NOT NULL,
	batch_key VARCHAR(64) NOT NULL,
	state VARCHAR(20) NOT NULL,
	updated_at DATETIME NOT NULL,

	PRIMARY KEY (order_id),
	KEY batch_state_order (
		batch_key,
		state,
		order_id
	),
	KEY updated_order (
		updated_at,
		order_id
	)
) ENGINE=InnoDB;

The literal wp_ prefix is illustrative. WordPress code must build the table name from $wpdb->prefix.

The composite index supports queries shaped like:

WHERE batch_key = ?
ORDER BY state, order_id

and:

WHERE batch_key = ?
  AND state = ?
ORDER BY order_id

It is not automatically useful for:

WHERE state = ?

because state is not the leftmost indexed column.

Store normalized values with appropriate types. Do not serialize a large JSON document and then expect the database to filter or sort efficiently by a nested property.

Inspect the actual plan

Use the database’s query-planning tools against production-like data:

EXPLAIN
SELECT order_id
FROM wp_hxf_order_export_lookup
WHERE batch_key = '2026-07'
	AND state = 'pending'
ORDER BY order_id
LIMIT 100;

On database versions supporting it, EXPLAIN ANALYZE can show the executed plan and measured row-processing behavior rather than only the optimizer’s estimates. (MySQL Developer Zone)

Inspect:

Chosen index
Estimated and actual row counts
Rows examined
Temporary tables
Filesorts
Join order
Filter selectivity
Execution time

Benchmark both reads and writes. A lookup table that makes a report fast but doubles checkout write time is not a successful optimization.

---

HPOS-safe administration list-screen queries

The HPOS order screen is not the old WordPress posts list table.

Code built around these legacy assumptions is therefore incomplete:

pre_get_posts
restrict_manage_posts
manage_edit-shop_order_columns
manage_shop_order_posts_custom_column

WooCommerce provides order-list hooks for adding controls, modifying query arguments, defining columns, and rendering column values under HPOS. (The WooCommerce Developer Blog)

Add a native-property filter

This example adds a payment-method filter to the orders screen:

function hxf_admin_payment_method_options(): array {
	return array(
		'bacs' => __(
			'Direct bank transfer',
			'hxf-orders'
		),
		'cod'  => __(
			'Cash on delivery',
			'hxf-orders'
		),
	);
}

add_action(
	'woocommerce_order_list_table_restrict_manage_orders',
	static function ( $order_type ): void {
		if ( 'shop_order' !== $order_type ) {
			return;
		}

		$current = isset(
			$_GET['hxf_payment_method']
		)
			? sanitize_key(
				wp_unslash(
					$_GET['hxf_payment_method']
				)
			)
			: '';

		echo '<select name="hxf_payment_method">';

		echo '<option value="">';
		echo esc_html__(
			'All payment methods',
			'hxf-orders'
		);
		echo '</option>';

		foreach (
			hxf_admin_payment_method_options()
			as $value => $label
		) {
			printf(
				'<option value="%1$s"%2$s>%3$s</option>',
				esc_attr( $value ),
				selected(
					$current,
					$value,
					false
				),
				esc_html( $label )
			);
		}

		echo '</select>';
	}
);

Apply the selected value through the HPOS list-table query arguments:

add_filter(
	'woocommerce_order_list_table_prepare_items_query_args',
	static function ( array $query_args ): array {
		$value = isset(
			$_GET['hxf_payment_method']
		)
			? sanitize_key(
				wp_unslash(
					$_GET['hxf_payment_method']
				)
			)
			: '';

		$allowed = array_keys(
			hxf_admin_payment_method_options()
		);

		if (
			$value
			&& in_array(
				$value,
				$allowed,
				true
			)
		) {
			$query_args['payment_method'] = $value;
		}

		return $query_args;
	}
);

This uses the first-class payment_method argument rather than modifying SQL or querying _payment_method metadata.

Add a custom order column

add_filter(
	'woocommerce_shop_order_list_table_columns',
	static function ( array $columns ): array {
		$columns['hxf_export_batch'] = __(
			'Export batch',
			'hxf-orders'
		);

		return $columns;
	}
);

add_action(
	'woocommerce_shop_order_list_table_custom_column',
	static function (
		string $column,
		$order
	): void {
		if (
			'hxf_export_batch' !== $column
			|| ! $order instanceof WC_Order
		) {
			return;
		}

		$value = $order->get_meta(
			'_hxf_export_batch',
			true
		);

		echo '' !== (string) $value
			? esc_html( (string) $value )
			: '—';
	},
	10,
	2
);

The action provides a WC_Order object. Use that existing object. Do not call get_post_meta() or perform another full order query for every table row.

HPOS and legacy admin hook mapping

Use case	HPOS order screen	Legacy posts screen
Add a filter control	woocommerce_order_list_table_restrict_manage_orders	restrict_manage_posts
Modify the order query	woocommerce_order_list_table_prepare_items_query_args	pre_get_posts
Add columns	woocommerce_shop_order_list_table_columns	manage_edit-shop_order_columns
Render a column	woocommerce_shop_order_list_table_custom_column	manage_shop_order_posts_custom_column
Adjust sortable columns	woocommerce_shop_order_list_table_sortable_columns	manage_edit-shop_order_sortable_columns

An extension supporting both datastores may need both sets of administration adapters. Keep the displayed business logic shared while isolating only the hook registration and query translation.

Avoid using the global woocommerce_order_query_args filter for a condition intended only for one administration screen. A global filter can unintentionally change REST requests, background jobs, reports, emails, and unrelated extension queries.

---

Testing with HPOS compatibility mode disabled

A compatibility declaration is meaningful only when the extension has been tested without the legacy datastore masking defects.

The minimum useful test environment is:

HPOS authoritative
Compatibility synchronization disabled
Fresh orders created after synchronization was disabled

Testing only previously synchronized orders is insufficient.

Step 1: Use a disposable staging copy

Back up the database and files before changing order storage.

Do not perform destructive HPOS cleanup on a live store merely to test an extension.

Step 2: Synchronize existing order data

The HPOS CLI namespace provides commands for enabling HPOS, synchronizing orders, verifying data, checking status, and cleaning legacy data. The previous wp wc cot namespace is deprecated in favor of wp wc hpos. (The WooCommerce Developer Blog)

A migration test can begin with:

wp wc hpos enable --with-sync
wp wc hpos sync
wp wc hpos verify_data
wp wc hpos status

Data verification is relevant while both datastores are available for comparison.

Step 3: Make HPOS authoritative

In:

WooCommerce
→ Settings
→ Advanced
→ Features

select High-Performance Order Storage as the authoritative datastore.

Wait until synchronization is complete before switching if the environment contains existing orders.

Step 4: Disable compatibility synchronization

Disable the setting that keeps HPOS order data synchronized with the WordPress posts tables.

Then verify:

wp wc hpos status

The important result is:

HPOS enabled: yes
Compatibility mode enabled: no

The exact formatting can vary between CLI releases, but both conditions must be true.

Step 5: Create fresh test orders

Create new orders after compatibility mode has been disabled.

Cover at least:

Fixture	Purpose
Pending unpaid order	Status and created-date queries
Processing order	Fulfilment and admin-screen queries
Completed order	Completion and paid-date queries
Guest order	Billing-email and customer-zero handling
Registered-customer order	customer_id queries
Refunded order	Parent and refund behavior
Order with custom metadata	HPOS meta_query
Different payment methods	Payment filtering
Different currencies	Currency filtering
Different billing countries	Address filtering
High-value order	field_query comparison
Order created after sync is off	Detect legacy-table dependencies

Then exercise:

Single-order loading
Order collections
Status filters
Customer filters
Payment filters
Address filters
Date filters
Pagination
Metadata reports
Administration filters
Custom columns
Exports
Scheduled actions
REST endpoints
Emails
Webhooks
Order updates
Refunds
Deletion and trash workflows

Step 6: Run a hard-mode cleanup test

On a disposable environment only, remove the synchronized legacy order data:

wp wc hpos cleanup all

This command is destructive. It removes legacy order data after HPOS is authoritative and compatibility mode is disabled. WooCommerce can retain lightweight placeholder posts to preserve IDs, but legacy order metadata is removed. (The WooCommerce Developer Blog)

This is one of the best ways to expose hidden calls to:

get_post()
get_posts()
WP_Query
get_post_meta()
update_post_meta()
delete_post_meta()
$wpdb->posts
$wpdb->postmeta

Do not use the command’s force options on production data to bypass safety checks.

Step 7: Run an automated integration test

The test environment should already be configured with HPOS authoritative and compatibility mode disabled.

use Automattic\WooCommerce\Utilities\OrderUtil;

final class HXF_Order_Query_Test
	extends WP_UnitTestCase {

	public function test_order_query_works_without_legacy_sync(): void {
		$this->assertTrue(
			OrderUtil::custom_orders_table_usage_is_enabled(),
			'HPOS must be active for this test.'
		);

		$this->assertNotSame(
			'yes',
			get_option(
				'woocommerce_custom_orders_table_data_sync_enabled',
				'no'
			),
			'Compatibility synchronization must be disabled.'
		);

		$order = wc_create_order();

		$order->set_status( 'processing' );
		$order->set_payment_method( 'bacs' );
		$order->set_billing_email(
			'hpos-query@example.test'
		);

		$order->update_meta_data(
			'_hxf_export_batch',
			'integration-test'
		);

		$order->save();

		$order_ids = wc_get_orders(
			array(
				'status'          => 'wc-processing',
				'payment_method'  => 'bacs',
				'billing_email'   =>
					'hpos-query@example.test',
				'limit'           => 10,
				'return'          => 'ids',
			)
		);

		$this->assertContains(
			$order->get_id(),
			array_map(
				'absint',
				$order_ids
			)
		);

		$metadata_matches = wc_get_orders(
			array(
				'meta_query' => array(
					array(
						'key'   =>
							'_hxf_export_batch',
						'value' =>
							'integration-test',
					),
				),
				'limit'      => 10,
				'return'     => 'ids',
			)
		);

		$this->assertContains(
			$order->get_id(),
			array_map(
				'absint',
				$metadata_matches
			)
		);
	}
}

The option assertion is appropriate in a test bootstrap. Production business logic should generally use WooCommerce utilities rather than reading internal options to decide how ordinary order operations work.

Recommended CI matrix

Job	Authoritative datastore	Compatibility mode	Purpose
Legacy compatibility	Posts	Off	Support older stores
HPOS compatibility	HPOS	Off	Required HPOS correctness test
Migration mode	HPOS	On	Optional synchronization-path test

Portable query tests should pass in both the legacy and HPOS jobs.

Tests using advanced HPOS-only meta_query, field_query, or date_query clauses should run only in the HPOS job or should explicitly test the extension’s fallback behavior.

WooCommerce’s large-store migration guidance similarly recommends verifying critical functionality after synchronization and compatibility mechanisms have been fully disabled, rather than assuming that a successful dual-write period proves native HPOS support. (The WooCommerce Developer Blog)

---

Declare HPOS compatibility only after the tests pass

Once the extension no longer depends on posts-based order storage, declare compatibility:

use Automattic\WooCommerce\Utilities\FeaturesUtil;

add_action(
	'before_woocommerce_init',
	static function (): void {
		if ( ! class_exists( FeaturesUtil::class ) ) {
			return;
		}

		FeaturesUtil::declare_compatibility(
			'custom_order_tables',
			__FILE__,
			true
		);
	}
);

When this code is not in the main plugin file, pass the main plugin file path rather than the current include file’s __FILE__.

The declaration should represent tested behavior, not an attempt to suppress a merchant warning. WooCommerce recommends auditing direct database access, converting order reads and writes to CRUD methods, updating administration hooks, and testing with HPOS enabled before declaring compatibility. (The WooCommerce Developer Blog)

---

HPOS-safe order-query checklist

Before approving an extension’s order-query layer, confirm that:

Query API

• Individual orders are loaded with wc_get_order().
• Order collections use wc_get_orders() or WC_Order_Query.
• Core properties use documented top-level arguments.
• No order query depends on WP_Query, get_posts(), or shop_order posts.
• No order query joins $wpdb->posts or $wpdb->postmeta.
• Result limits are always intentional.
• Background jobs process bounded, resumable batches.
• return => 'ids' is used when objects are unnecessary.
• paginate => true is used only when totals are needed.

Dates and metadata

• Date strings and UTC timestamps are not mixed accidentally.
• Portable top-level date arguments are preferred.
• Advanced date_query use is guarded as HPOS-only.
• Core properties are not queried through historical meta keys.
• Custom metadata is read and written through WC_Order.
• HPOS meta_query and field_query use is tested.
• Legacy fallback behavior is explicit when legacy storage remains supported.

Performance

• Queries include selective status, customer, date, or property constraints.
• Large exports are divided into stable windows.
• Custom metadata is not used as an unbounded search engine.
• Frequently filtered custom dimensions have an appropriate lookup strategy.
• Index decisions are based on measured query plans.
• Additional indexes are tested for write impact.
• Administration columns do not perform external calls per row.

Administration

• HPOS order-list hooks are used.
• Column callbacks consume the supplied WC_Order.
• List filters modify documented order query arguments.
• Screen-specific filters do not globally alter all WooCommerce queries.
• Legacy administration adapters exist only when legacy support is intentional.

Testing

• HPOS is authoritative.
• Compatibility synchronization is disabled.
• Fresh orders are created after synchronization is disabled.
• Query, admin, REST, export, and background-job paths are tested.
• A disposable cleanup test exposes hidden post-table dependencies.
• The extension passes a separate HPOS-without-sync CI job.
• Compatibility is declared only after the complete suite passes.

---

Conclusion

HPOS-safe querying is not primarily about learning a new table schema.

It is about refusing to make the table schema part of the extension’s business logic.

The unsafe model is:

Order
→ WordPress post
→ post type and post meta
→ direct query

The supported model is:

Order requirement
→ WooCommerce query arguments
→ WC_Order_Query or wc_get_orders()
→ active WooCommerce datastore

That separation allows WooCommerce to use HPOS today and evolve its storage implementation later without forcing every extension to rewrite its reports, exports, administration filters, and background jobs again.

Use first-class order arguments whenever possible. Treat advanced metadata, field, and date clauses as HPOS-only. Paginate deliberately. Add indexes only after measuring actual workloads. Use WooCommerce’s list-table APIs in administration. Most importantly, test with compatibility synchronization disabled and orders created after it was turned off.

If the extension still works under those conditions, it is not merely compatible with an HPOS migration.

It is actually querying WooCommerce orders through the supported abstraction.

Official references

• WooCommerce order-query API and supported arguments. (The WooCommerce Developer Blog)
• HPOS extension recipe book and compatibility guidance. (The WooCommerce Developer Blog)
• HPOS architecture and synchronization model. (The WooCommerce Developer Blog)
• Advanced HPOS query clauses. (The WooCommerce Developer Blog)
• HPOS WP-CLI commands and cleanup behavior. (The WooCommerce Developer Blog)
• WooCommerce HPOS performance benchmark. (The WooCommerce Developer Blog)
• WooCommerce administration list-table hooks. (The WooCommerce Developer Blog)
• MySQL index and execution-plan guidance. (MySQL Developer Zone)