如何使用多个标记快速搜索文章/列表?

kmb7vmvb  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(361)

我的目录站点目前使用“toxi”表结构来标记列表。
我的posts表有大约500万条记录,map表有大约1500万条记录。
我使用术语来存储各种信息,如作者、出版商、主题、媒体(如音频、视频等)。一篇文章可以为每个分类法提供多个术语(多个作者、多个主题等)。
基于一个术语id搜索帖子大约需要4秒钟才能返回结果,这非常糟糕,但是使用多个术语返回结果需要40秒钟。
我需要一个更有效的解决方案,但我不知道是我的查询效率低下还是我的表结构。
==单词搜索查询==

SELECT * FROM posts
LEFT JOIN post_taxonomy_term_map ON (posts.ID = post_taxonomy_term_map.object_id)
WHERE post_taxonomy_term_map.term_id=$term1

==多词搜索查询==

SELECT p.*
FROM post_taxonomy_term_map m, posts p
WHERE m.term_id IN ($term1, $term2, $term3)
AND p.ID = m.object_id
GROUP BY p.ID
HAVING COUNT( p.ID )=3

表和列

帖子{id,帖子标题等…}
主要id
分类法术语{term\u id,term\u label,term\u slug,etc.}
主要术语\u id
post\u taxonomy\u term\u map{map\u id,object\u id,taxonomy,term\u id}
主Mapid
索引对象\u id
索引项\u id
索引分类法
注意:post\u taxonomy\u term\u map.object\u id与posts.id值相关

nmpmafwu

nmpmafwu1#

可能主要的性能问题是由于many:many table 毒物的要求。可以消除:

CREATE TABLE Tags (
    tag VARHAR(...) NOT NULL,
    bid INT ... NOT NULL,
    PRIMARY KEY(tag, bid),
    INDEX(bid, tag)
)

笔记:
这比toxi好,因为它不会经过额外的many:many table,这使得优化变得困难。
当然,由于冗余标记的存在,我的方法可能会稍显笨重(比toxi),但这只占整个数据库的一小部分,性能改进可能会非常显著。
它具有高度的可扩展性。
它没有(因为它不需要)代理 AUTO_INCREMENT 主键。因此,它比煤斗好。
mysqlicious很糟糕,因为它不能使用索引( LIKE 具有领先的通配符;子字符串错误命中)
对于mysql,确保使用engine=innodb以获得“集群”效果。
相关讨论(针对mysql):
many:many mapping 表优化,
有序列表,
而且,特别是对于wp用户,posttea改进

lskq00tm

lskq00tm2#

对于您的第一个查询:

SELECT *
FROM posts
LEFT JOIN post_taxonomy_term_map
    ON posts.ID = post_taxonomy_term_map.object_id
WHERE post_taxonomy_term_map.term_id = $term1

上的综合指数 (term_id, object_id) . 该指数包括 term_id ,其中一个或多个索引已经在执行,但它还通过包含 object_id . 假设 WHERE 子句被优化器视为具有足够的限制性来使用索引,这应该比您当前拥有的性能更好。

CREATE INDEX some_idx ON post_taxonomy_term_map(term_id, object_id);

相关问题