我目前使用自定义wordpress MySQL查询来获取我的一些购物网站的相关产品。我刚刚介绍了我的页面,我注意到这个查询大约需要3秒,但我不确定如何优化它。查询如下:
explain select
p . *,
unix_timestamp(p.post_modified) as post_modified_ut,
unix_timestamp(p.post_date) as post_date_ut,
(((2.3 * (MATCH (p.post_title) AGAINST ('Motorola+MBP+36+Digital+Video+Monitor' IN BOOLEAN MODE)))) + (0.6 * (MATCH (p.post_content) AGAINST ('Motorola+MBP+36+Digital+Video+Monitor' IN BOOLEAN MODE)))) AS relevance
from
wp_posts as p,
wp_terms as t,
wp_term_taxonomy as tt,
wp_term_relationships as tr
where
(MATCH (p.post_title , p.post_content) AGAINST ('Motorola+MBP+36+Digital+Video+Monitor' IN BOOLEAN MODE))
and tr.object_id = p.ID
and tr.term_taxonomy_id = tt.term_taxonomy_id
and tt.term_id = t.term_id
and p.post_type = 'post'
and p.post_status in ('inherit' , 'publish')
group by p.ID , p.post_title
order by relevance desc
limit 5;
解释的结果是:
+----+-------------+-------+--------+-------------------------------------------------+------------------+---------+------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------------+------------------+---------+------------------------------------+------+---------------------------------+
| 1 | SIMPLE | tt | ALL | PRIMARY,term_id_taxonomy | NULL | NULL | NULL | 2822 | Using temporary; Using filesort |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 8 | reviewexplorer.tt.term_id | 1 | Using index |
| 1 | SIMPLE | tr | ref | PRIMARY,term_taxonomy_id | term_taxonomy_id | 8 | reviewexplorer.tt.term_taxonomy_id | 5 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY,type_status_date,searches,searches_more | PRIMARY | 8 | reviewexplorer.tr.object_id | 1 | Using where |
+----+-------------+-------+--------+-------------------------------------------------+------------------+---------+------------------------------------+------+---------------------------------+
正如你所看到的,我正在使用临时表,我不想,我想创建一个索引来加快这个查询,但我不完全理解解释告诉我什么。
显然,MySQL无法在wp_term_taxonomy
表上使用任何索引,必须在临时表中检查并排序所有2822行。话虽如此,2822行并没有大到可以解释如此长的查询时间。数据库或磁盘上的负载高吗?无论如何
通过更仔细地查看您的查询,它会非常令人困惑。显然,你有一个wp_term_taxonomy.term_taxonomy_id
和wp_term_relationships.term_taxonomy_id
,但一个可能的索引在wp_term_taxonomy.term_id_taxonomy
上。
看到区别了吗?term_taxonomy_id
≠term_id_taxonomy
。
这是打字错误吗?一个错误?"特色"?