mysql like％query是慢全文索引 [英] mysql like% query is slow with full-text index

问题描述

我使用一个简单的mysql LIKE查询，像这样：

SELECT * FROM myTable WHERE字段LIKE'aaa％'ORDER BY field2

I'm using a simple mysql LIKE query like this:
SELECT * FROM myTable WHERE field LIKE 'aaa%' ORDER BY field2

我有一个关于field的全文索引，但仍然很慢。
我明白有一个选项使用匹配。有什么不同？怎么样？什么是我的使用的最佳方法？
注意我对以aaa开头的所有内容使用％

I have a full-text index on "field", and still it is very slow. I understood there is an option to use match. What is the difference? How? What is the best approach for my usage? Notice I'm using the "%" for everything that starts with "aaa"

更新：
我最后使用这个：
SELECT

*，MATCH（name）AGAINST（'a *'IN BOOLEAN MODE）AS SCORE

FROM

用户
WHERE

MATCH（名称）AGAINST（'a *'IN BOOLEAN模式）

按比例排序，人气DESC LIMIT 4

我想改变的一件事是，不是首先通过SCORE然后通过我的领域流行度，而是通过一个简单的权重函数，像0.5 * SCORE + 0.5 *的人气。如何？

One thing I would like to change, is not due the order by firstly by SCORE and then by my field popularity, and instead order by a simple weight function, something like 0.5*SCORE + 0.5*popularity. How?

推荐答案

LIKE 不使用全文索引。要使用全文索引，必须使用match（如你所说）：

LIKE does not use the full-text index. To make use of the fulltext index, you have to use match (as you said):

SELECT * 
FROM myTable 
WHERE MATCH(field) AGAINST ('aaa*' IN BOOLEAN MODE)
ORDER BY field2

b $ b

MySQL手册有大量的全文搜索功能。

这篇关于mysql like％query是慢全文索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！