使用Match for Match加快MySQL查询搜索多个表

Question

这是我第一次尝试构建更复杂的搜索功能，而不是使用类似功能。从此搜索中，返回的结果非常完美，但运行速度确实很慢。我有什么能改善代码以加快速度或我应该在数据库上查看的东西吗？还是我需要查看更多的服务器电源？

感谢很多帮助。非常感谢！

function new_live_search($q){
    $title_score = 5;
    $tags_score = 10;
    $upvote_score = 1;
    $subdomain = $this->config->item('subdomain_name');
    $query = "SELECT DISTINCT results.*,
                ( 
                    ".$title_score."*(MATCH(title) AGAINST('$q' IN BOOLEAN MODE)) + 
                    ".$tags_score."*(MATCH(tags.name) AGAINST('$q' IN BOOLEAN MODE)) + 
                    ".$upvote_score."*usefulness
                ) AS score
                FROM results
                LEFT JOIN tags ON results.id = tags.result_id
                WHERE (scope = 'all' OR scope = '$subdomain') 
                        AND (published = 1)";
    $query .=  "
        HAVING score - usefulness > 0 
        ORDER BY score DESC, title";
    $query = $this->db->query($query);
    $results = array();

    foreach ($query->result() as $row){
        $results[] = $row;
    }
    return $results;
}

Answer 1

来自mySQL文档

不幸的是，不可能将完整字段和正常（即整数）字段组合到一个索引中。由于每个查询只能使用一个索引，所以这似乎是一个问题

表布局：

id(integer primary key)|content(text fulltext indexed)|status(integer key)

请注意，在查询之后执行，MySQL将仅使用一个索引。fullText或状态（取决于实习生统计）。

查询1：

SELECT * FROM table WHERE MATCH(content) AGAINST('searchQuery') AND status = 1

但是，仍然可以在一个查询中使用两个索引。您将需要在ID，状态对上进行新的索引，并使用加入。因此，MySQL将能够为每个表使用一个索引。

查询2：

SELECT t1.* FROM table t1
LEFT JOIN table t2 ON(t1.id=t2.id)
WHERE MATCH(t1.content) AGAINST('searchQuery') AND status=1

查询2的运行速度要比查询1快得多，至少在我的情况下:)注意开销：您将需要每行的ID和一个以ID开头的字段上跨越的键。

在MySQL文档上参考完整搜索
希望它能帮助您

Answer 2

如果查看查询，则查询的完整部分实际上不会限制搜索。使用以下内容应提高性能。

SELECT DISTINCT results.*, (
    $title_score * (MATCH(title) AGAINST('$q' IN BOOLEAN MODE)) + 
    $tags_score * (MATCH(tags.name) AGAINST('$q' IN BOOLEAN MODE)) + 
    $upvote_score * usefulness
) AS score
FROM results
LEFT JOIN tags ON results.id = tags.result_id
WHERE (scope = 'all' OR scope = '$subdomain') 
    AND (published = 1)
    AND (
        (MATCH(title) AGAINST('$q' IN BOOLEAN MODE)) OR
        (MATCH(tags.name) AGAINST('$q' IN BOOLEAN MODE)))
HAVING score - usefulness > 0 
ORDER BY score DESC, title