我继承了一个大型的、丑陋的mysql 5.7数据库，并希望优化一些查询。
具体来说，用户希望搜索7个不同的 var_char 列…在7个不同的表中。
显然，我可以加入7个表并筛选 LIKE '%search term%' ，但我想用 MATCH...AGAINST 一 FULLTEXT 索引。
我创建了索引：

ALTER TABLE table1 ADD FULLTEXT INDEX (origname);
ALTER TABLE table2 ADD FULLTEXT INDEX (byline);
ALTER TABLE table3 ADD FULLTEXT INDEX (copyright);
ALTER TABLE table4 ADD FULLTEXT INDEX (source);
ALTER TABLE table5 ADD FULLTEXT INDEX (image_title);
ALTER TABLE table6 ADD FULLTEXT INDEX (photo_info);
ALTER TABLE table7 ADD FULLTEXT INDEX (alt_text);

我尝试了下面的方法，但是性能很差…有没有好的方法可以跨多个表进行全文搜索？

SELECT
    MATCH(table1.name) AGAINST ('search term') as name,
    MATCH(table2.byline) AGAINST ('search term') as byline,
    MATCH(table3.copyright) AGAINST ('search term') as copyright,
    MATCH(table4.source) AGAINST ('search term') as copyright,
    MATCH(table5.image_title) AGAINST ('search term') as image_title,
    MATCH(table6.photo_info) AGAINST ('search term') as photo_info,
    MATCH(table7.alt_text) AGAINST ('search term') as alt_text
FROM table1
LEFT JOIN table2 on table2.entity_id = table1.fid
LEFT JOIN table3 on table3.entity_id = table1.fid
LEFT JOIN table4 on table4.entity_id = table1.fid
LEFT JOIN table5 on table5.entity_id = table1.fid
LEFT JOIN table6 on table6.entity_id = table1.fid
LEFT JOIN table7 ON table7.alt_text = table1.fid
WHERE
    MATCH(table1.name) AGAINST ('search term') OR
    MATCH(table2.byline) AGAINST ('search term') OR
    MATCH(table3.copyright) AGAINST ('search term') OR
    MATCH(table4.source) AGAINST ('search term') OR
    MATCH(table5.image_title) AGAINST ('search term') OR
    MATCH(table6.photo_info) AGAINST ('search term') OR
    MATCH(table7.alt_text) AGAINST ('search term');

理想情况下，我只会使用elasticsearch/lucene/solr等，但让我们假设一下，我不能使用任何这些东西，我仅限于mysql。有什么好办法吗？
这是你的名字 EXPLAIN EXTENDED 输出：

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra   
1   SIMPLE  table1  NULL    ALL NULL    NULL    NULL    NULL    557535  100 NULL    
1   SIMPLE  table2  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
1   SIMPLE  table3  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
1   SIMPLE  table4  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
1   SIMPLE  table5  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
1   SIMPLE  table6  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
1   SIMPLE  table7  NULL    ALL field_file_image_alt_text_value NULL    NULL    NULL    203374  100 Using where

===========================================
回答：bomar下面的解决方案是正确的方法，但是MySQL5.7不喜欢他的语法。我必须这样编码：
选择fid，scoredname作为分数，origname作为“文件名”，null作为署名，null作为版权，null作为源，null作为“图片标题”，null作为“照片信息”，null作为“alt text”

SELECT fid, MATCH(origname) AGAINST ('search term') AS scoredName, origname, NULL AS N4, NULL AS N5, NULL AS N6, NULL AS N7, NULL AS N8, NULL AS N9
    FROM table1
    WHERE MATCH(origname) AGAINST ('search term')

    UNION

    SELECT entity_id, MATCH(byline) AGAINST ('search term') AS scoredByline, NULL AS N3, byline, NULL AS N5, NULL AS N6, NULL AS N7, NULL AS N8, NULL AS N9
    FROM table2   
    WHERE MATCH (byline) AGAINST ('search term')

    UNION

    SELECT entity_id, MATCH(copyright) AGAINST ('search term') AS scoredCopyright, NULL AS N3, NULL AS N4, copyright, NULL AS N6, NULL AS N7, NULL AS N8, NULL AS N9
    FROM table3
    WHERE MATCH (copyright) AGAINST ('search term')

    UNION

    SELECT entity_id, MATCH(source) AGAINST ('search term') AS scoredSource, NULL AS N3, NULL AS N4, NULL AS N5, source, NULL AS N7, NULL AS N8, NULL AS N9
    FROM table4
    WHERE MATCH (source) AGAINST ('search term')

    UNION

    SELECT entity_id, MATCH(image_title) AGAINST ('search term') AS scoredTitle, NULL AS N3, NULL AS N4, NULL AS N5, NULL AS N6, image_title, NULL AS N8, NULL AS N9
    FROM table5
    WHERE MATCH(image_title) AGAINST ('search term')

    UNION

    SELECT entity_id, MATCH(full_photo_info) AGAINST ('search term') AS scoredPhotoInfo, NULL AS N3, NULL AS N4, NULL AS N5, NULL AS N6, NULL AS N7, full_photo_info, NULL AS N9
    FROM table6
    WHERE MATCH(full_photo_info) AGAINST ('search term')

    UNION

    SELECT entity_id, MATCH(file_image_alt_text) AGAINST ('search term') as scoredaltText, NULL AS N3, NULL AS N4, NULL AS N5, NULL AS N6, NULL AS N7, NULL AS N8, file_image_alt_text
    FROM table7
    WHERE MATCH(file_image_alt_text) AGAINST ('search term')
) AS g
GROUP BY fid, score, origname, byline, copyright, source, "Image title", "Photo info", "Alt text"
ORDER BY score DESC;

解释： UNION 只是将后续查询连接到由第一个查询设置的列中。
因此，它们都必须有相同数量的列，这就是为什么您会看到所有这些列 NULL 价值观。
但是不能有两个同名的列，因此必须分别使用别名 NULL .
下一步，我可能会用更重要的指数乘以一个常数来加权不同的指数。