MySQL 连接和全文搜索

Question

有全文的 MySQL 专家吗？

我昨天写了一个问题，询问有关如何使用大量连接的全文搜索的任何建议，有点懒，我实际上没有尝试过。

从那时起，我就尝试自己掌握它，针对我的全文字段构建了一些成功的测试查询。如果我不使用任何连接，并单独查询每个字段，则全文函数可以完美运行，相关性/ bool 值也可以很好地工作，性能也很好......但是......一旦我添加我的连接以运行完整查询, 它会永远运行下去。

任何人都可以在我的查询中发现任何可能导致这种情况发生的东西吗，因为作为一个业余爱好者，我真的看不到它!

SELECT

photos.photoID,
photos.headline,
photos.dateCreated,
MATCH (people.people) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Rel1

FROM photos

LEFT JOIN ( photoPeople INNER JOIN people ON photoPeople.peopleID = people.PeopleID ) 
ON photos.photoID = photoPeople.photoID AND MATCH (people.people) AGAINST ('+sarah +harding' IN BOOLEAN MODE)

WHERE

photos.photoStatus = 'Live'

GROUP BY

photos.photoID

ORDER BY Rel1

这是我成功的个人查询之一:

SELECT

photoID,
headline,
dateCreated,
MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Relevance

FROM photos

WHERE

photoStatus = 'Live'

AND

MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE)

ORDER BY Relevance

这是数据库架构:

photos (tbl)
photoID             INT(11)         Primary     Auto-Increment
headline            Long-Text
caption             Long-Text / FULLTEXT
dateCreated         DateTime

people (tbl)
peopleID            INT(11)         Primary     Auto-Increment
people              VarChar(255) / FULLTEXT

photoPeople (tbl)
photoID             INT(11)
peopleID            INT(11)

keywords (tbl)
keywordID           INT(11)         Primary     Auto-Increment
keyword             VarChar(255) / FULLTEXT

photoKeyword (tbl)
photoID             INT(11)
keywordID           INT(11)

photoContributor (tbl)
photoID             INT(11)
contributorRef      VarChar(100) / FULLTEXT

这是我的 EXPLAIN 输出:

id  select_type         table               type        possible_keys       key     key_len     ref     rows1   SIMPLE              photos              ALL         NULL                NULL        NULL        NULL        898301   SIMPLE              photoContributor    ALL         NULL                NULL        NULL        NULL        149635  1   SIMPLE              photoPeople         ALL         NULL                NULL        NULL        NULL        1106061   SIMPLE              people              eq_ref      PRIMARY             PRIMARY     4       1   1   SIMPLE              photoKeyword        ALL         NULL                NULL        NULL        NULL        699102  1   SIMPLE              keywords            eq_ref      PRIMARY             PRIMARY     4       1

我的网站访问者应该能够搜索:“Brad Pitt Angelina Jolie Dorchester Hotel Sunglasses @MG”——这应该从“people.people”表中找到“Brad Pitt”和“Angelina Jolie”，也可能从“photos.caption”表中找到。它还应该从“photos.caption”表中找到“Dorchester Hotel”，从“keywords.keyword”表中找到“Sunglasses”，最后，它应该在“photoContributor.contributorRef”表中找到“@MG”。

如有任何帮助，我们将不胜感激......

Answer 1

我建议使用单独的较小查询并使用 UNION 来创建组合结果集。

这是另一个具有类似任务的问题。 Mysql Full text search across more than one table

更新

SELECT "Photos" AS TableName, photoID AS ID, MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Relevance
FROM photos
UNION ALL
SELECT "People" AS TableName, peopleID AS ID, MATCH (people) AGAINST ('+sarah +harding' IN BOOLEAN MODE)
FROM people
UNION ALL 
SELECT "Keyword" AS TableName, keywordID AS ID, MATCH (keyword) AGAINST ('+sarah +harding' IN BOOLEAN MODE)
FROM keyword

我真的不知道你有什么数据，但它会产生类似的东西:

表名 | 身份证 | 相关性

照片 | 1 | 1

照片 | 2 | 0

人物 | 1 | 1

关键词 | 10 | 1

您可以添加额外的 where 子句以满足您的需要，但这是基本思想。