基于标签的搜索与MySQL [英] Tag based searching with MySQL

问题描述

我想写在MySQL中基于标签的搜索引擎，但我​​真的不知道怎么去一个愉快的结果。

I want to write a tag based search engine in MySQL, but I don't really know how to get to a pleasant result.

我以前喜欢，但我保存时间超过18K的关键词在数据库中，这是pretty的慢。

I used LIKE, but as I stored over 18k keywords in the database, it's pretty slow.

我得到的是一个表是这样的：

What I got is a table like this:

ID（INT，主键）article_cloud（文字）的关键字（VARCHAR（40），全文索引）

id(int, primary key) article_cloud(text) keyword(varchar(40), FULLTEXT INDEX)

所以，我店每行一个关键字，并保存所有。参见文章编号，以article_cloud。

So I store one keyword per row and save all the refering article numbers in article_cloud.

我试过MATCH（）反对（）的东西，罚款，只要它的工作原理，在整个关键字的用户类型。但是，我也希望有一个建议的搜索，以便有相关的文章雨后春笋般冒出来，当用户输入。所以我还需要一个类似的声明很像，但速度更快。我不知道我能做什么。

I tried the MATCH() AGAINST() stuff, which works fine as long as the user types in the whole keyword. But I also want a suggest search, so that there are relevant articles popping up, while the user is typing. So I still need a similar statement to LIKE, but faster. And I have no idea what I could do.

也许这是个错误的基于标签的搜索的概念。如果你知道一个更好的，请让我知道。我打这个好几天，找不出一个满意的解决方案。感谢您的阅读：）

Maybe this is the wrong concept of tag based searching. If you know a better one, please let me know. I'm fighting with this for days and can't figure out a satisfying solution. Thanks for reading :)

推荐答案

MATCH（）反对（）/ FULLTEXT搜索快速解决一个问题 - 但你的方案是没有意义的 - 肯定有每个多个关键字文章？并采用上一列只包含一个字一个全文索引是相当愚蠢的。

MATCH() AGAINST() / FULLTEXT searching is a quick fix to a problem - but your schema makes no sense at all - surely there are multiple keywords in each article? And using a fulltext index on a column which only contains a single word is rather dumb.

和保存所有。参见文章编号，以article_cloud

and save all the refering article numbers in article_cloud

没有！在一列存储多个值是非常不好的做法。当这些值键到另一个表，这是一个弥天大罪！

No! storing multiple values in a single column is VERY bad practice. When those values are keys to another table, it's a mortal sin!

看起来你已经有了很长的路要走，你创造的东西，这将有效地工作;的目标最快的路线可能是对你自己的数据使用谷歌或雅虎的索引服务。但是，如果你想自己解决这个问题......

It looks like you've got a long journey ahead of you to create something which will work efficiently; the quickest route to the goal is probably to use Google or Yahoo's indexing services on your own data. But if you want to fix it yourself....

<一个href="http://stackoverflow.com/questions/5486371/search-for-a-string-using-mysql-and-php/5486564#5486564">See创建一个搜索引擎这个答案 - 关键字应该是在一个单独的表有N：1的关系，你的文章，对关键字和文章编号，例如主键

See this answer on creating a search engine - the keywords should be in a separate table with a N:1 relationship to your articles, primary key on keyword and article id, e.g.

CREATE TABLE article (
    id INTEGER NOT NULL autoincrement,
    modified TIMESTAMP,
    content TEXT
    ...
    PRIMARY KEY (id)
);

CREATE TABLE keyword (
    word VARCHAR(20),
    article_id INTEGER, /* references article.id
    relevance FLOAT DEFAULT 0.5, /* allow users to record relevance of keyword to article*/
    PRIMARY KEY (word, article_id)
);

CREATE TEMPORARY TABLE search (
    word VARCHAR(20),
    PRIMARY KEY (word)
);

然后分割由用户输入的词语，它们转换到一致的情况下（等同于用于填充关键字表），并填充搜索表，然后找到使用匹配....

Then split the words entered by the user, convert them to a consistent case (same as used for populating the keyword table) and populate the search table, then find matches using....

SELECT article.id, SUM(keyword.relevance)
FROM article, keyword, search
WHERE article.id=keyword.article_id
AND keyword.word=search.word
GROUP BY article_id
ORDER BY SUM(keyword.relevance) DESC
LIMIT 0,3

这将是一个很大更有效率，如果你能保持大约字不为关键字用文字或规则的列表（如无视3个字符或更少的混合或小写的任何话都会忽略的东西，如A ，到，是，和，他是...）。

It'll be a lot more efficient if you can maintain a list of words or rules about words NOT to use as keywords (e.g. ignore any words of 3 chars or less in mixed or lower case will omit stuff like 'a', 'to', 'was', 'and', 'He'...).

这篇关于基于标签的搜索与MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！