mysql - 在这种情况下如何提高搜索速度呢？

Question

我的网站上实现了搜索，它运行以下查询:

SELECT COUNT(mov_id) AS total_things 
FROM content
    WHERE con_status = 1 AND con_incomplete = 0 AND con_type = 1 
            AND ((con_title) LIKE ('%search keyword%') 
            OR soundex(con_title) LIKE soundex('search keyword')
            OR MATCH (con_title) AGAINST ('search keyword'));

+----+-------------+--------+------+---------------+----------+---------+-------------------+-------+-------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref               | rows  | Extra       |
+----+-------------+--------+------+---------------+----------+---------+-------------------+-------+-------------+
|  1 | SIMPLE      | movies | ref  | con_type      | con_type | 12      | const,const,const | 11804 | Using where |
+----+-------------+--------+------+---------------+----------+---------+-------------------+-------+-------------+

64058 条查询

总时间:200817，平均时间:3.13492459958163

需要 2 到 25 秒才能完成

分析的行数 1882 - 12104

<小时/>

SELECT 
            con_id, 
            con_title, 
            con_desc,
            MATCH (con_title) AGAINST ('search keyword') AS relevancy 
    FROM content
    WHERE con_status = 1 AND con_incomplete = 0 AND con_type = 1 
            AND ((con_title) LIKE ('%search keyword%') 
            OR soundex(con_title) LIKE soundex('search keyword')
            OR MATCH (con_title) AGAINST ('search keyword')) 
    ORDER BY relevancy DESC 
    LIMIT 0, 24;

+----+-------------+--------+------+---------------+----------+---------+-------------------+-------+-----------------------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref               | rows  | Extra                       |
+----+-------------+--------+------+---------------+----------+---------+-------------------+-------+-----------------------------+
|  1 | SIMPLE      | movies | ref  | con_type      | con_type | 12      | const,const,const | 11803 | Using where; Using filesort |
+----+-------------+--------+------+---------------+----------+---------+-------------------+-------+-----------------------------+

78321 条查询

总时间:200657，平均时间:2.56198209930925

需要 2 到 16 秒才能完成

分析的行数 0 - 15752

这基本上就像贫民窟的“模糊搜索”一样，忽略人们可能犯的拼写错误。

不幸的是，它非常慢(即使我删除了 soundex() 或 FULLTEXT 搜索。在这种情况下如何提高搜索速度？

Answer 1

WHERE 子句中最糟糕的部分是 LIKE 之后的第一个 %。为了加快速度，您可以标准化关键字，将它们移动到单独的表中:

table moviekeywords: movieid, keyword
table movies:        movieid, ...

这允许您使用 = 条件或至少如“humphrey%” 来搜索 moviekeywords 表。这两种变体都可以通过索引变得非常快。