最后几天,当我尝试在管理仪表板搜索输入中搜索产品时,我得到的查询速度很慢(4+秒sql答案(。
查询监视器 4,782 秒(也直接在 mysql cli 中执行查询,我得到相同的时间( wp_psotmeta表是"大"200.000 行
SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id
FROM wp_posts posts
LEFT JOIN wp_postmeta postmeta ON posts.ID = postmeta.post_id
WHERE ( posts.post_title LIKE '%INVMCB278A2W%'
OR posts.post_excerpt LIKE '%INVMCB278A2W%'
OR posts.post_content LIKE '%INVMCB278A2W%'
OR ( postmeta.meta_key = '_sku'
AND postmeta.meta_value LIKE '%INVMCB278A2W%' ) )
AND posts.post_type IN ('product','product_variation')
ORDER BY posts.post_parent ASC, posts.post_title ASC
如何减少响应时间(不使用缓存(
+-------------------------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
| wp_aryo_activity_log | InnoDB | 10 | Compact | 26 | 630 | 16384 | 0 | 0 | 0 | 143 | 2018-06-11 21:31:04 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_aws_cache | InnoDB | 10 | Compact | 0 | 0 | 49152 | 0 | 0 | 6291456 | NULL | 2018-06-11 21:31:04 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_aws_index | InnoDB | 10 | Compact | 365747 | 104 | 38338560 | 0 | 0 | 7340032 | NULL | 2018-06-11 21:31:05 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_commentmeta | InnoDB | 10 | Compact | 101 | 162 | 16384 | 0 | 32768 | 0 | 115 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_comments | InnoDB | 10 | Compact | 866 | 359 | 311296 | 0 | 294912 | 0 | 975 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_layerslider | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_layerslider_revisions | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_links | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_masterslider_options | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 2 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_masterslider_sliders | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 2 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_newsletter | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 32768 | 0 | 6 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_newsletter_emails | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_newsletter_sent | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_newsletter_stats | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_newsletter_user_logs | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_omega_sync_status | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 0 | 0 | NULL | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_options | InnoDB | 10 | Compact | 7556 | 873 | 6602752 | 0 | 1507328 | 4194304 | 409398 | 2018-06-11 21:31:12 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_piraeus_bank_transactions | InnoDB | 10 | Compact | 9 | 1820 | 16384 | 0 | 0 | 0 | 10 | 2018-06-11 21:31:13 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_piraeusbank_transactions | InnoDB | 10 | Compact | 153 | 107 | 16384 | 0 | 0 | 0 | 188 | 2018-06-11 21:31:13 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_postmeta | InnoDB | 10 | Compact | 212550 | 175 | 37306368 | 0 | 24215552 | 4194304 | 245092 | 2018-06-11 21:31:13 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_posts | InnoDB | 10 | Compact | 17042 | 1016 | 17317888 | 0 | 4128768 | 4194304 | 25216 | 2018-06-11 21:31:20 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_revslider_css | InnoDB | 10 | Compact | 109 | 1202 | 131072 | 0 | 0 | 0 | 110 | 2018-06-11 21:31:22 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_revslider_layer_animations | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:22 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_revslider_navigations | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 2 | 2018-06-11 21:31:22 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_revslider_sliders | InnoDB | 10 | Compact | 2 | 24576 | 49152 | 0 | 0 | 0 | 3 | 2018-06-11 21:31:22 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_revslider_slides | InnoDB | 10 | Compact | 6 | 57344 | 344064 | 0 | 0 | 0 | 22 | 2018-06-11 21:31:22 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_revslider_static_slides | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:22 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_slp_extendo_meta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 0 | 1 | 2018-06-11 21:31:23 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_store_locator | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 0 | 1 | 2018-06-11 21:31:23 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_term_relationships | InnoDB | 10 | Compact | 32541 | 48 | 1589248 | 0 | 1589248 | 4194304 | NULL | 2018-06-11 21:31:23 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_term_taxonomy | InnoDB | 10 | Compact | 2862 | 85 | 245760 | 0 | 294912 | 0 | 3120 | 2018-06-11 21:31:23 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_termmeta | InnoDB | 10 | Compact | 3510 | 70 | 245760 | 0 | 376832 | 0 | 3756 | 2018-06-11 21:31:23 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_terms | InnoDB | 10 | Compact | 2862 | 85 | 245760 | 0 | 278528 | 0 | 3120 | 2018-06-11 21:31:23 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_usermeta | InnoDB | 10 | Compact | 2380 | 96 | 229376 | 0 | 245760 | 0 | 4391 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_users | InnoDB | 10 | Compact | 56 | 292 | 16384 | 0 | 49152 | 0 | 59 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_wc_download_log | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_wc_webhooks | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_woocommerce_api_keys | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_attribute_taxonomies | InnoDB | 10 | Compact | 181 | 271 | 49152 | 0 | 16384 | 0 | 197 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_downloadable_product_permissions | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 49152 | 0 | 1 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_log | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_order_itemmeta | InnoDB | 10 | Compact | 6854 | 69 | 475136 | 0 | 442368 | 0 | 9572 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_order_items | InnoDB | 10 | Compact | 1037 | 94 | 98304 | 0 | 49152 | 0 | 1478 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_payment_tokenmeta | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_payment_tokens | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_sessions | InnoDB | 10 | Compact | 4842 | 5755 | 27869184 | 0 | 491520 | 5242880 | 105968 | 2018-06-11 21:31:24 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_shipping_table_rates | InnoDB | 10 | Compact | 774 | 127 | 98304 | 0 | 0 | 0 | 781 | 2018-06-11 21:31:26 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_woocommerce_shipping_zone_locations | InnoDB | 10 | Compact | 1317 | 74 | 98304 | 0 | 147456 | 0 | 3500 | 2018-06-11 21:31:26 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_shipping_zone_methods | InnoDB | 10 | Compact | 195 | 84 | 16384 | 0 | 0 | 0 | 207 | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_shipping_zones | InnoDB | 10 | Compact | 67 | 244 | 16384 | 0 | 0 | 0 | 71 | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_tax_rate_locations | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woocommerce_tax_rates | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 65536 | 0 | 4 | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woof_query_cache | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | NULL | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woof_stat_buffer | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_woof_stat_tmp | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_ci | NULL | | |
| wp_wpgmza | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | 5 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_wpgmza_categories | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_wpgmza_category_maps | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_wpgmza_circles | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_wpgmza_maps | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 2 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_wpgmza_polygon | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_wpgmza_polylines | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_wpgmza_rectangles | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2018-06-11 21:31:27 | NULL | NULL | utf8_general_ci | NULL | | |
| wp_yoast_seo_links | InnoDB | 10 | Compact | 99 | 165 | 16384 | 0 | 16384 | 0 | 995 | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
| wp_yoast_seo_meta | InnoDB | 10 | Compact | 2089 | 54 | 114688 | 0 | 0 | 0 | NULL | 2018-06-11 21:31:27 | NULL | NULL | utf8mb4_unicode_520_ci | NULL | | |
+-------------------------------------------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+------------------------+----------+----------------+---------+
Mysql 解释命令
+------+-------------+----------+------+------------------+---------+---------+-----------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+------------------+---------+---------+-----------------------+-------+----------------------------------------------+
| 1 | SIMPLE | posts | ALL | type_status_date | NULL | NULL | NULL | 17042 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | postmeta | ref | post_id | post_id | 8 | theodoulidis.posts.ID | 8 | Using where; Distinct |
+------+-------------+----------+------+------------------+---------+---------+-----------------------+-------+----------------------------------------------+
优化方式太多
WHERE ( posts.post_title LIKE '%INVMCB278A2W%'
OR posts.post_excerpt LIKE '%INVMCB278A2W%'
OR posts.post_content LIKE '%INVMCB278A2W%'
OR ( postmeta.meta_key = '_sku'
AND postmeta.meta_value LIKE '%INVMCB278A2W%' ) )
AND posts.post_type IN ('product','product_variation')
ORDER BY posts.post_parent ASC, posts.post_title ASC
索引是必需的,但是...
OR
禁止使用索引。- 两张桌子之间的
OR
使情况变得更糟。 - 具有前导通配符的
LIKE
禁止使用索引。 IN (x, y)
(多个选项(可防止将其与ORDER BY
一起使用。- 如果没有其他一些需要"修复"的东西,
UNION
代替OR
将非常混乱。 FULLTEXT
索引可以跨多个列工作,但只能在单个表中工作。- 但从技术上讲
"INVMCB278A2W"
是一个"词"吗? - 有一些方法可以加快后元,但其他问题阻止了它们。
解决这里和其他给你的提示,然后回来看看它是否足够接近我们来帮助完成任务。
我遇到了同样的问题,没有多少索引或优化帮助(63,000 种产品(。
我把查询拆开,发现是SKU搜索减慢了速度。
经过几个小时的深入研究Woocommerce的代码,我终于找到了查询的形成位置并删除了SKU搜索。
要自己执行此操作,请自行编辑/wp-content/plugins/woocommerce/include/data-stores/class-wc-product-data-store-cpt.php文件,查找search_products函数并从以"$term_group_query .=">开头的行中删除 SKU 搜索(WC 版本 3.4.4 中的第 1397 行(
如果需要,您还可以在第 1423 行的下方完全删除wp_postmeta的连接。
显然,如果您想实际搜索SKU,这不是一个合适的解决方案。
您可以在表的相关列上创建索引,例如;
ID, post_title, post_parent in wp_posts table,
post_id, wp_postmeta table
还要为 varchar 字段放置索引,并在左侧重写没有野生 (%( 的 where 条件。例如:
posts.post_title LIKE '%INVMCB278A2W%'
不会在列上使用索引post_title 但
posts.post_title LIKE 'INVMCB278A2W%'
将使用索引
这是一个非常小众的问题,我们的团队刚刚在一个简单的WooCommerce上遇到了这个问题,它开始有大量的产品(1k +(。就像@CrashM提到的,问题出在class-wc-product-data-store-cpt中找到的SQL查询的SKU部分.php在"search_products"函数中,在第1687行附近。
最佳解决方案是简单地覆盖函数,这要归功于可以在同一函数中较早找到的过滤器。过滤器称为"woocommerce_product_pre_search_products",返回产品 ID 将完全绕过函数的其余部分,因此您可以通过复制部分函数甚至您自己的函数、缓存结果等来实现更简单的查询。
以下是我想出的快速解决问题的方法,因为我们不需要在任何时候按任何元字段搜索,除了有时按 SKU 搜索,所以我添加了一些糖,如果搜索词以"sku"开头,则通过启动不同的 MySQL 查询来重新允许按 SKU 搜索:"或"SKU:"。
我没有添加一般的"is_admin"检查,因为我们的问题也发生在前端,过滤器也在那里启动,但如果管理员的可过滤下拉列表是您的唯一问题,您可以在整个代码周围添加"is_admin"条件以避免它干扰产品搜索插件, 等。
/**
* Solves the post_meta bottleneck caused by SKU and other metas being included uselessly while searching products
* Mostly replicates the code in search_products at class-wc-product-data-store-cpt.php:1585 and adds a condition
* to re-allow searching by sku if the search query starts with "SKU:" or "sku:"
*/
add_filter('woocommerce_product_pre_search_products', function($unused, $term, $type = '', $include_variations = false, $all_statuses = false, $limit = null) {
global $wpdb;
$post_types = $include_variations ? array( 'product', 'product_variation' ) : array( 'product' );
$status_where = '';
$limit_query = '';
/**
* Hook woocommerce_search_products_post_statuses.
*
* @since 3.7.0
* @param array $post_statuses List of post statuses.
*/
$post_statuses = apply_filters(
'woocommerce_search_products_post_statuses',
current_user_can( 'edit_private_products' ) ? array( 'private', 'publish' ) : array( 'publish' )
);
if ( ! $all_statuses ) {
$status_where = " AND posts.post_status IN ('" . implode( "','", $post_statuses ) . "') ";
}
if ( $limit ) {
$limit_query = $wpdb->prepare( ' LIMIT %d ', $limit );
}
/**
* Check if search starts with "SKU:" or "sku:"
*/
$start_check_sku = strtoupper(substr($term, 0, 4));
if($start_check_sku === 'SKU:') {
$search_term = substr($term, 4);
$like = '%' . $wpdb->esc_like( $search_term ) . '%';
$skusearch = $wpdb->prepare( " AND wc_product_meta_lookup.sku LIKE %s ", $like );
$search_results = $wpdb->get_results(
// phpcs:disable
"SELECT DISTINCT posts.ID as product_id FROM {$wpdb->posts} posts
LEFT JOIN {$wpdb->wc_product_meta_lookup} wc_product_meta_lookup ON posts.ID = wc_product_meta_lookup.product_id
WHERE posts.post_type IN ('" . implode( "','", $post_types ) . "')
$skusearch
$status_where
$limit_query
"
// phpcs:enable
);
$product_ids = wp_list_pluck($search_results, 'product_id');
}else {
// See if search term contains OR keywords.
if ( stristr( $term, ' or ' ) ) {
$term_groups = preg_split( '/s+ors+/i', $term );
} else {
$term_groups = array( $term );
}
$search_where = '';
$search_queries = array();
foreach ( $term_groups as $term_group ) {
// Parse search terms.
if ( preg_match_all( '/".*?("|$)|((?<=[t ",+])|^)[^t ",+]+/', $term_group, $matches ) ) {
// $search_terms = $this->get_valid_search_terms( $matches[0] );
$search_terms = array();
$stopwords = apply_filters( 'wp_search_stopwords', array_map(
'wc_strtolower',
array_map(
'trim',
explode(
',',
_x(
'about,an,are,as,at,be,by,com,for,from,how,in,is,it,of,on,or,that,the,this,to,was,what,when,where,who,will,with,www',
'Comma-separated list of search stopwords in your language',
'woocommerce'
)
)
)
));
foreach ( $matches[0] as $term ) {
// keep before/after spaces when term is for exact match, otherwise trim quotes and spaces.
if ( preg_match( '/^".+"$/', $term ) ) {
$term = trim( $term, ""'" );
} else {
$term = trim( $term, ""' " );
}
// Avoid single A-Z and single dashes.
if ( empty( $term ) || ( 1 === strlen( $term ) && preg_match( '/^[a-z-]$/i', $term ) ) ) {
continue;
}
if ( in_array( wc_strtolower( $term ), $stopwords, true ) ) {
continue;
}
$search_terms[] = $term;
}
$count = count( $search_terms );
// if the search string has only short terms or stopwords, or is 10+ terms long, match it as sentence.
if ( 9 < $count || 0 === $count ) {
$search_terms = array( $term_group );
}
} else {
$search_terms = array( $term_group );
}
$term_group_query = '';
$searchand = '';
foreach ( $search_terms as $search_term ) {
$like = '%' . $wpdb->esc_like( $search_term ) . '%';
$term_group_query .= $wpdb->prepare( " {$searchand} ( ( posts.post_title LIKE %s) )", $like, $like, $like ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$searchand = ' AND ';
}
if ( $term_group_query ) {
$search_queries[] = $term_group_query;
}
}
if ( ! empty( $search_queries ) ) {
$search_where = ' AND (' . implode( ') OR (', $search_queries ) . ') ';
}
if ( ! empty( $include ) && is_array( $include ) ) {
$search_where .= ' AND posts.ID IN(' . implode( ',', array_map( 'absint', $include ) ) . ') ';
}
if ( ! empty( $exclude ) && is_array( $exclude ) ) {
$search_where .= ' AND posts.ID NOT IN(' . implode( ',', array_map( 'absint', $exclude ) ) . ') ';
}
// phpcs:ignore WordPress.VIP.DirectDatabaseQuery.DirectQuery
$search_results = $wpdb->get_results(
// phpcs:disable
"SELECT DISTINCT posts.ID as product_id, posts.post_title as post_title, posts.post_parent as parent_id FROM {$wpdb->posts} posts
WHERE posts.post_type IN ('" . implode( "','", $post_types ) . "')
$search_where
$status_where
ORDER BY posts.post_parent ASC, posts.post_title ASC
$limit_query
"
// phpcs:enable
);
$product_ids = wp_parse_id_list( array_merge( wp_list_pluck( $search_results, 'product_id' ), wp_list_pluck( $search_results, 'parent_id' ) ) );
if ( is_numeric( $term ) ) {
$post_id = absint( $term );
$post_type = get_post_type( $post_id );
if ( 'product_variation' === $post_type && $include_variations ) {
$product_ids[] = $post_id;
} elseif ( 'product' === $post_type ) {
$product_ids[] = $post_id;
}
$product_ids[] = wp_get_post_parent_id( $post_id );
}
}
return wp_parse_id_list( $product_ids );
}, 10, 6);
试试这个 -
要添加的索引:
ALTER TABLE `wp_postmeta` ADD INDEX `wp_postmeta_idx_key_id_value` (`meta_key`,`post_id`,`meta_value`);
ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_type_parent_title_id` (`post_type`,`post_parent`,`post_title`,`ID`);
ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_content_parent_title_id` (`post_content`,`post_parent`,`post_title`,`ID`);
ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_excerpt_parent_title_id` (`post_excerpt`,`post_parent`,`post_title`,`ID`);
ALTER TABLE `wp_posts` ADD INDEX `wp_posts_idx_title_parent_id` (`post_title`,`post_parent`,`ID`);
要执行的查询:
SELECT
*
FROM
((SELECT
posts.ID AS product_id,
posts.post_parent AS parent_id,
posts.post_title
FROM
wp_posts posts
WHERE
(
(
1 = 1
AND 1 = 1
)
)
AND posts.post_type IN (
'product', 'product_variation'
)
AND EXISTS (
SELECT
*
FROM
wp_postmeta postmeta
WHERE
posts.ID = postmeta.post_id
AND postmeta.meta_key = '_sku'
AND postmeta.meta_value LIKE '%INVMCB278A2W%'
)
ORDER BY
posts.post_parent ASC,
posts.post_title ASC)
UNION
DISTINCT (SELECT
DISTINCT posts.ID AS product_id,
posts.post_parent AS parent_id,
posts.post_title
FROM
wp_posts posts
WHERE
(posts.post_content LIKE '%INVMCB278A2W%')
AND posts.post_type IN ('product', 'product_variation')
ORDER BY
posts.post_parent ASC,
posts.post_title ASC)
UNION
DISTINCT (SELECT
DISTINCT posts.ID AS product_id,
posts.post_parent AS parent_id,
posts.post_title
FROM
wp_posts posts
WHERE
(posts.post_excerpt LIKE '%INVMCB278A2W%')
AND posts.post_type IN ('product', 'product_variation')
ORDER BY
posts.post_parent ASC,
posts.post_title ASC)
UNION
DISTINCT (SELECT
DISTINCT posts.ID AS product_id,
posts.post_parent AS parent_id,
posts.post_title
FROM
wp_posts posts
WHERE
(posts.post_title LIKE '%INVMCB278A2W%')
AND posts.post_type IN ('product', 'product_variation')
ORDER BY
posts.post_parent ASC,
posts.post_title ASC)
) AS union1
ORDER BY
union1.post_parent ASC,
union1.post_title ASC