Hey rin, I’d be glad to share my changes, but they do require you to change woocommerce plugin files, so first I’ll give the disclaimer that editing plugin files are not a good practice, as they get overwritten by updating the plugin and there is always a chance of breaking the plugin/site.
Here are some base stats of search times w/ 125,000 orders.
- Customer first/last name: 70 seconds
- Order number: 30 seconds
- Other Fields: 55 seconds
I’ll post each level of change I made and the search time stats with each change. You can choose what changes you would like to include based on your search needs.
First off, this is all done in woocommerce->includes->wc-order-functions.php. Find “function wc_order_search”.
First change:
Remove all search fields you don’t need to search by. I removed all of them except _order_key and _billing_email (first and last names are handled later in this function). With this one change, I increased search times by:
- Customer first/last name: 50 seconds
- Order number: 10 seconds
- Other Fields: 50 seconds
Second change:
I separated searching for a first/last name from the search fields. The way this works is if there is a space in your search (as you would have for a first and last name), it will search for names only, but any search without a space, will go on to search for all the fields you decided to keep in $search_fields. *Warning* If you search with spaces, it will ignore all the fields and search for first and last name only. Also, if you want to search by only first name or only last name, please include those fields in your $search_fields array.
To do this, replace the entire if/else statement with this:
if ( is_numeric( $term ) ) {
$post_ids = array_unique( array_merge(
$wpdb->get_col(
$wpdb->prepare( “SELECT DISTINCT p1.post_id FROM {$wpdb->postmeta} p1 WHERE p1.meta_key IN (‘” . implode( “‘,'”, array_map( ‘esc_sql’, $search_fields ) ) . “‘) AND p1.meta_value LIKE ‘%%%s%%’;”, wc_clean( $term ) )
),
array( absint( $term ) )
) );
} elseif ( strpos($term, ” “) !== false ){
$post_ids = array_unique( array_merge(
$wpdb->get_col(
$wpdb->prepare( “
SELECT DISTINCT p1.post_id
FROM {$wpdb->postmeta} p1
INNER JOIN {$wpdb->postmeta} p2 ON p1.post_id = p2.post_id
WHERE
( p1.meta_key = ‘_billing_first_name’ AND p2.meta_key = ‘_billing_last_name’ AND CONCAT(p1.meta_value, ‘ ‘, p2.meta_value) LIKE ‘%%%s%%’ )
OR
( p1.meta_key = ‘_shipping_first_name’ AND p2.meta_key = ‘_shipping_last_name’ AND CONCAT(p1.meta_value, ‘ ‘, p2.meta_value) LIKE ‘%%%s%%’ )
“,
$term, $term, $term
)
),
$wpdb->get_col(
$wpdb->prepare( “
SELECT order_id
FROM {$wpdb->prefix}woocommerce_order_items as order_items
WHERE order_item_name LIKE ‘%%%s%%’
“,
$term
)
)
) );
} elseif ( ! empty( $search_fields ) ) {
$post_ids = array_unique( array_merge(
$wpdb->get_col(
$wpdb->prepare( “
SELECT DISTINCT post_id
FROM {$wpdb->postmeta}
WHERE
( meta_key IN (‘” . implode( “‘,'”, array_map( ‘esc_sql’, $search_fields ) ) . “‘) AND meta_value LIKE ‘%%%s%%’ )
“,
$term
)
),
$wpdb->get_col(
$wpdb->prepare( “
SELECT order_id
FROM {$wpdb->prefix}woocommerce_order_items as order_items
WHERE order_item_name LIKE ‘%%%s%%’
“,
$term
)
)
) );
}
Search time results after this change are:
- Customer first/last name: 45 seconds
- Order number: 10 seconds
- Other Fields: 10 seconds
Third change:
Remove search for shipping first and last name, only search for billing first and last name (or can vice versa) found in the first elseif. The code you remove is:
OR
( p1.meta_key = ‘_shipping_first_name’ AND p2.meta_key = ‘_shipping_last_name’ AND CONCAT(p1.meta_value, ‘ ‘, p2.meta_value) LIKE ‘%%%s%%’ )
Search time results:
- Customer first/last name: 25 seconds
- Order number: 10 seconds
- Other Fields: 10 seconds
Hope this is helpful to some of you. The correct way to implement this (if someone has the time) is by adding a dropdown next to the search field on the admin orders page that is populated from $search_fields, then the search query is modified to whatever you select. This would guarantee the quickest search results time. I don’t have the time to figure this out unfortunately (sounds like a needed feature for order searches).
