优化MySQL全文搜索

Question

我有一个只有一个字段的搜索，允许我在MySQL表的几个列中搜索。

这是SQL查询：

SELECT td__user.*
FROM td__user LEFT JOIN td__user_oauth ON td__user.id = td__user_oauth.user_id 
WHERE ( td__user.id LIKE "contact@mywebsite.com" OR (MATCH (email, firstname, lastname) AGAINST (+"contact@mywebsite.com" IN BOOLEAN MODE)) ) 
ORDER BY date_accountcreated DESC LIMIT 20 OFFSET 0

使用PHP对搜索字段进行预处理来分隔每个单词的确切SQL查询：

if($_POST['search'] == '') {
    $searchId = '%';
} else {
    $searchId = $_POST['search'];
}

$searchMatch = '';
foreach($arrayWords as $word) {
    $searchMatch .= '+"'.$word.'" ';
}

$sqlSearch = $dataBase->prepare('SELECT td__user.*,
                                        td__user_oauth.facebook_id, td__user_oauth.google_id
                                 FROM td__user
                                 LEFT JOIN td__user_oauth ON td__user.id = td__user_oauth.user_id
                                 WHERE (
                                         td__user.id LIKE :id OR 
                                         (MATCH (email, firstname, lastname) AGAINST (:match IN BOOLEAN MODE)) ) 

                                 ORDER BY date_accountcreated DESC LIMIT 20 OFFSET 0);

$sqlSearch->execute(['id'    => $searchId,
                     'match' => $searchMatch]);

$searchResult = $sqlSearch->fetchAll();
$sqlSearch->closeCursor();

这些是我的索引：

​

SQL查询运行良好，当我在搜索字段中输入一个ID、一个名字或一个姓氏，或者一封电子邮件甚至没有完成，我就有结果了。我也可以在我的搜索字段中放一个名字和一个姓，我只会给出这个名字的人。

另一方面，在包含50万联系人的表中，查询时间超过5秒。为了实现更快的查询，在查询或索引中是否有需要改进的地方？

Answer 1

您是否尝试使用"UNION“2组结果，而不是在"WHERE”子句中使用"OR“运算符？因为我只是害怕索引不能和"OR“操作符一起使用。

查询将如下所示：

SELECT td__user.*,
    td__user_oauth.facebook_id, 
    td__user_oauth.google_id
FROM td__user
LEFT JOIN td__user_oauth ON td__user.id = td__user_oauth.user_id
WHERE td__user.id LIKE :id

UNION
SELECT td__user.*,
    td__user_oauth.facebook_id, 
    td__user_oauth.google_id
FROM td__user
LEFT JOIN td__user_oauth ON td__user.id = td__user_oauth.user_id
WHERE MATCH (email, firstname, lastname) AGAINST (:match IN BOOLEAN MODE))

ORDER BY date_accountcreated DESC LIMIT 20 OFFSET 0

希望这能帮上忙！

Answer 2

FULLTEXT是快的；LIKE和领先的%是慢的；OR是慢的。

考虑这一办法：

1. 运行查询的MATCH部分。如果没有结果，则运行LIKE查询，但只允许跟踪通配符。