MySQL
无法索引视图,所以你想要的东西是不可能的MySQL
.
您可以使用 Sphinx 等外部全文索引引擎,并使用以下查询加载数据:JOINS
或者,您可以创建非规范化表:
CREATE TABLE ftsearch
(
teacherId INT PRIMARY KEY,
teacherBiography TEXT,
subject1 TEXT,
subject2 TEXT,
subject3 TEXT,
)
ENGINE=MyISAM
并用以下查询填充它:
INSERT
INTO ftsearch
SELECT teacherId, teacherBiography,
s1.name, s2.name, s3.name
FROM teacherProfile
LEFT JOIN
subject s1
ON s1.id = subjectOneId
LEFT JOIN
subject s2
ON s2.id = subjectTwoId
LEFT JOIN
subject s3
ON s3.id = subjectThreeId
及时。
实际上,如果你所有的桌子都是MyISAM
,您可以将全文搜索查询(布尔模式)应用于联接,而无需创建全文索引。
比如说,如果你正在寻找'+Jones +math +physics'
, where Jones
是老师的姓氏并且math
and physics
是主题,您可以执行以下查询:
SELECT teacherId, teacherBiography,
s1.name, s2.name, s3.name
FROM teacherProfile
LEFT JOIN
subject s1
ON s1.id = subjectOneId
LEFT JOIN
subject s2
ON s2.id = subjectTwoId
LEFT JOIN
subject s3
ON s3.id = subjectThreeId
WHERE MATCH(teacherBiography) AGAINST ('+Jones' IN BOOLEAN MODE)
AND MATCH(t.teacherBiography, s1.name, s2.name, s3.name) AGAINST ('+Jones +math +physics' IN BOOLEAN MODE)
The MATCH(teacherBiography) AGAINST ('+Jones')
将使用一个FULLTEXT
索引于teacher
,如果有的话;第二MATCH
将对结果进行精细过滤。
涉及的查询OR
然而,条件或相关性排序更为复杂。