MySQL 全文搜索和连接

Question

我建立了一个名人照片网站。达到 100,000 张照片后，我的业余技能开始显现，需要快速磨练。一些简单的查询需要 5-10 秒才能返回!

我有一个单文本框搜索功能，它将搜索“照片”表(用于标题和标题)、“人物”表、“关键字”表和“photoContributor”表。 “人物”和“关键词”有两个关系/关联表，分别称为“photoPeople”和“photoKeyword”。

一个可能的用户搜索可能是:“Brad Pitt Angelina Jolie Sunglasses @MG”- 这应该返回包含 Brad 和 Angelina 的所有照片，贡献者 @MG 可以在其中看到和拍摄太阳镜。

我很快意识到我不能在“照片”表中的“标题”和“标题”字段上使用索引，因为我使用的是带有“%”前缀的“LIKE”子句，而且那些特定字段被设置为“LONGTEXT”数据类型。因为我在这些字段上没有索引，所以返回时间很长。因此，我相信我需要使用“FULLTEXT”搜索，然后我可以将“标题”和“标题”字段设置为 VARCHAR(2000)，因为迄今为止最大的标题是 1991 个字符，并且仍然使用“INDEX”功能，这有望加快速度。我也喜欢从搜索中删除单词的 bool 函数。

我写这个问题的原因是我对“JOINS”完全没用。我可能可以在一张表上写一个“FULLTEXT”查询，我也可以使用左连接或右连接将两个表连接在一起……但是因为我在两者之间有关联/关系表，所以我真的非常困惑。

如果有人可以给我看一个示例查询，使用连接和全文以及使用关系表，我将不胜感激，或者只是向我解释要使用哪个连接，如果有的话，以及你对全文的任何提示我拥有的数据库结构。

下面是我的基本数据库架构:

photos (tbl)photoID             INT(11)         Primary     Auto-Incrementheadline            Long-Textcaption             Long-TextdateCreated         DateTimepeople (tbl)peopleID            INT(11)         Primary     Auto-Incrementpeople              VarChar(255)photoPeople (tbl)photoID             INT(11)peopleID            INT(11)keywords (tbl)keywordID           INT(11)         Primary     Auto-Incrementkeyword             VarChar(255)photoKeyword (tbl)photoID             INT(11)keywordID           INT(11)photoContributor (tbl)photoID             INT(11)contributorRef      VarChar(100)

进行搜索时，查询的表/字段是:photos.headline, photos.caption, keywords.keyword, people.people, photoContributor.contributorRef.

我希望有人能帮助我构建这个紧急查询。

Answer 1

同时具有全文搜索和外键约束(这对连接很有用)对 MySQL 来说是一个麻烦，因为它们分别仅由 MyISAM 和 InnoDB 引擎支持。

为了进行全文搜索，您必须拥有 MyIsam 引擎的表格。我在那里没有那么多经验，所以我帮不了你太多。

如您所料，时间消耗是因为您使用 like 和 % 前缀，强制数据库在返回之前查看表中的每个该死的条目。

如果您想使用联接，则必须在轮询数据库之前拆分查询字符串，但这应该相当容易。可以在 W3school 教程中找到对连接的一个很好的介绍。 http://www.w3schools.com/sql/default.asp

我看到您遇到的主要问题是:即使你设法在你的数据库中实现了一些像样的连接，你仍然需要在连接表上使用 like(因为你不能在 InnoDB 上进行全文搜索)。因此，花哨的连接不会大大加快您的查询速度。

我的建议是:创建更多搜索字段。这样做将使从连接中获得一些好处成为可能。将人名拆分为名字、中间姓氏以避免需要使用 Like。

如果您真的想保留一个文本字段搜索，您可能需要制定一些关于如何输入数据的约定(以便您可以在幕后将其拆分并进行搜索)，就像为贡献者添加前缀一样。

很抱歉，我不能比这更精确和更有帮助，但我担心你所呈现的不是一个快速解决的问题。