Optimizing product search in a WooCommerce site with a large number of products (i.e., over 5,000) is crucial for improving performance and user experience. One effective approach is to create a custom lookup table. This table can be tailored to your specific search needs, reducing the complexity of queries and speeding up the search process.
Here’s a detailed, step-by-step explanation on how to create and implement a custom lookup table for WooCommerce product search:
Step 1: Create the Custom Lookup Table
First, you need to create a custom lookup table in your WordPress database. This table will store essential search data, such as product IDs, titles, SKUs, and other relevant meta information.
You can create the table using a plugin or via direct SQL execution. Here’s how you can do it using a custom plugin:
<?php
/*
Plugin Name: WooCommerce Custom Product Lookup Table
Description: Creates a custom lookup table for WooCommerce product search optimization.
Version: 1.0
Author: Your Name
*/
if (!defined('ABSPATH')) {
exit; // Exit if accessed directly.
}
class WC_Custom_Product_Lookup_Table {
public function __construct() {
register_activation_hook(__FILE__, array($this, 'create_lookup_table'));
add_action('save_post', array($this, 'update_lookup_table'), 10, 2);
}
// Function to create the custom lookup table
public function create_lookup_table() {
global $wpdb;
$table_name = $wpdb->prefix . 'custom_product_lookup';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
product_id BIGINT(20) UNSIGNED NOT NULL,
product_title TEXT NOT NULL,
product_sku VARCHAR(100) NOT NULL,
meta_data TEXT NOT NULL,
PRIMARY KEY (product_id)
) $charset_collate;";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);
}
// Function to update the lookup table when a product is saved
public function update_lookup_table($post_id, $post) {
if ($post->post_type !== 'product') {
return;
}
global $wpdb;
$table_name = $wpdb->prefix . 'custom_product_lookup';
// Get product data
$product = wc_get_product($post_id);
$product_title = $product->get_name();
$product_sku = $product->get_sku();
// Get any meta data you want to store (e.g., custom fields, attributes)
$meta_data = ''; // Serialize or concatenate meta data as needed
// Insert or update the lookup table
$wpdb->replace(
$table_name,
array(
'product_id' => $post_id,
'product_title' => $product_title,
'product_sku' => $product_sku,
'meta_data' => $meta_data
),
array('%d', '%s', '%s', '%s')
);
}
}
new WC_Custom_Product_Lookup_Table();Step 2: Populate the Lookup Table
After creating the table, you need to populate it with data from your existing products. You can write a custom script to loop through all products and insert relevant data into the table.
Here’s an example of how to do it:
function populate_custom_product_lookup_table() {
global $wpdb;
$table_name = $wpdb->prefix . 'custom_product_lookup';
$products = wc_get_products(array(
'limit' => -1 // Get all products
));
foreach ($products as $product) {
$product_id = $product->get_id();
$product_title = $product->get_name();
$product_sku = $product->get_sku();
// Get any meta data you want to store
$meta_data = ''; // Serialize or concatenate meta data as needed
$wpdb->replace(
$table_name,
array(
'product_id' => $product_id,
'product_title' => $product_title,
'product_sku' => $product_sku,
'meta_data' => $meta_data
),
array('%d', '%s', '%s', '%s')
);
}
}
// Run this function to populate the table
populate_custom_product_lookup_table();Step 3: Modify the Search Query to Use the Lookup Table
Now that you have a custom lookup table populated with product data, you need to modify WooCommerce’s product search query to use this table instead of the default search mechanism.
You can do this by hooking into the posts_search filter:
function custom_product_search_query($search, $wp_query) {
global $wpdb;
// Check if this is a WooCommerce product search query
if (!is_search() || !isset($wp_query->query_vars['s'])) {
return $search;
}
$search_term = $wp_query->query_vars['s'];
// Build the custom query using the lookup table
$lookup_table = $wpdb->prefix . 'custom_product_lookup';
$search = $wpdb->prepare(
"AND {$wpdb->posts}.ID IN (
SELECT product_id
FROM $lookup_table
WHERE product_title LIKE %s
OR product_sku LIKE %s
OR meta_data LIKE %s
)",
'%' . $wpdb->esc_like($search_term) . '%',
'%' . $wpdb->esc_like($search_term) . '%',
'%' . $wpdb->esc_like($search_term) . '%'
);
return $search;
}
add_filter('posts_search', 'custom_product_search_query', 10, 2);Step 4: Testing and Optimization
- Testing: After implementing the above steps, thoroughly test the product search functionality. Make sure the search results are accurate and the performance has improved.
- Optimization: Depending on the complexity of your products and the amount of data, you might want to index specific columns, optimize the table structure, or refine the search query for better performance.
Step 5: Maintain the Lookup Table
Finally, ensure the lookup table remains up to date. The save_post action will handle updates when products are added or modified, but you might also need to account for product deletions, which you can handle via the before_delete_post action:
function remove_from_custom_product_lookup_table($post_id) {
if (get_post_type($post_id) !== 'product') {
return;
}
global $wpdb;
$table_name = $wpdb->prefix . 'custom_product_lookup';
$wpdb->delete(
$table_name,
array('product_id' => $post_id),
array('%d')
);
}
add_action('before_delete_post', 'remove_from_custom_product_lookup_table');Summary
By creating and maintaining a custom lookup table for WooCommerce product search, you can significantly improve search performance, especially on a large store with thousands of products. This approach reduces the complexity of search queries, leading to faster and more efficient searches.
Leave a Reply