mysql - 无法更改 wordpress query.php 中的查询值

Question

我的网站具有 woo commerce 主题，加载速度非常慢，特别是产品摘要页面加载几乎需要 3 分钟。现在我想更改产品摘要页面主查询，但找不到方法以便我能够更改/优化我的查询。查询输出值如下所示。我在 Query.php 中更改 $query，但是当我执行页面时，查询没有更改，并且显示相同的查询。如果需要任何其他更改，请指导。下面给出了 query.php 中的先前值和新值。

之前的值

$query = $wpdb->prepare("SELECT  post_id
    FROM  $wpdb->postmeta, $wpdb->posts
    WHERE  ID = post_id
      AND  post_type = %s
      AND  meta_key = '_wp_old_slug'
      AND  meta_value = %s",
    $post_type, $wp_query->query_vars['name']);

新值(想使用这个值而不是上面的值)

$query = $wpdb->prepare("SELECT post_id
          FROM $wpdb->postmeta, $wpdb->posts
          WHERE ID = post_id" );

用于页面数据的查询值

SELECT SQL_CALC_FOUND_ROWS wp_bkkf_posts.ID
FROM wp_bkkf_posts
INNER JOIN wp_bkkf_postmeta
ON ( wp_bkkf_posts.ID = wp_bkkf_postmeta.post_id )
WHERE 1=1
AND ( ( wp_bkkf_postmeta.meta_key = '_visibility'
AND wp_bkkf_postmeta.meta_value IN ('visible','catalog') ) )
AND wp_bkkf_posts.post_type = 'product'
AND (wp_bkkf_posts.post_status = 'publish'
OR wp_bkkf_posts.post_status = 'private')
GROUP BY wp_bkkf_posts.ID
ORDER BY wp_bkkf_posts.menu_order ASC, wp_bkkf_posts.post_title ASC
LIMIT 0, 12

实际上我不想在查询中使用以下值。

AND ( ( wp_bkkf_postmeta.meta_key = '_visibility'
AND wp_bkkf_postmeta.meta_value IN ('visible','catalog') ) )
AND wp_bkkf_posts.post_type = 'product'
AND (wp_bkkf_posts.post_status = 'publish'
OR wp_bkkf_posts.post_status = 'private')

显示创建表输出如下

CREATE TABLE `wp_bkkf_postmeta` (                                                               
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,                                          
`post_id` bigint(20) unsigned NOT NULL DEFAULT '0',                                             
`meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,                                
`meta_value` longtext COLLATE utf8mb4_unicode_ci,                                               
PRIMARY KEY (`meta_id`),                                                                        
KEY `post_id` (`post_id`),                                                                      
KEY `meta_key` (`meta_key`(191))                                                                
) ENGINE=MyISAM AUTO_INCREMENT=30883570 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci      

CREATE TABLE `wp_bkkf_posts` (                                                                  
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,                                               
`post_author` bigint(20) unsigned NOT NULL DEFAULT '0',                                         
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',                                    
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',                                
`post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,                                    
`post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,                                          
`post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,                                        
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',                
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',                
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',                   
`post_password` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',                     
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',                        
`to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,                                             
`pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,                                              
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',                                
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',                            
`post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,                           
`post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',                                         
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',                             
`menu_order` int(11) NOT NULL DEFAULT '0',                                                      
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',                     
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',                   
`comment_count` bigint(20) NOT NULL DEFAULT '0',                                                
PRIMARY KEY (`ID`),                                                                             
KEY `post_name` (`post_name`(191)),                                                             
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),                            
KEY `post_parent` (`post_parent`),                                                              
KEY `post_author` (`post_author`),                                                              
KEY `post_idx` (`post_type`,`post_status`)                                                      
) ENGINE=MyISAM AUTO_INCREMENT=1556181 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci     

如果有人对 $query 值更改有任何想法，请指导，也请分享需要更改的其他文件名..谢谢。

Answer 1

SELECT MAX(CHAR_LENGTH(meta_key)) FROM wp_bkkf_postmeta;

如果结果小于 191，则执行以下操作:

ALTER TABLE wp_bkkf_postmeta
    MODIFY `meta_key` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    DROP KEY `post_id`,
    ADD INDEX pm (post_id, meta_key);

考虑删除SQL_CALC_FOUND_ROWS；这可能是一种放缓。然而，丢失该内容将会丢失页面上的“out of ...”子句。

如果你扔掉整个 WHERE，那么 JOIN 和 GROUP BY 也会消失:

SELECT  SQL_CALC_FOUND_ROWS wp_bkkf_posts.ID
    FROM  wp_bkkf_posts
    ORDER BY  wp_bkkf_posts.menu_order ASC, wp_bkkf_posts.post_title ASC
    LIMIT  0, 12

这样会运行得更快。尽管如此，它仍然需要扫描整个 150 万行的表。为什么您需要特定的ORDER BY？