像%查询这样的mysql在全文索引上速度很慢

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

我使用一个简单的mysql查询，比如:SELECT * FROM myTable，其中字段为field2的‘aaa%’ORDER

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"

我有一个关于“field”的全文索引，但它仍然很慢。我知道可以使用match。的区别是什么?如何?我使用的最好方法是什么?注意，我用"%"表示所有以"aaa"开头的项

UPDATE: I've ended up using something like this: SELECT *, MATCH (name) AGAINST ('a*' IN BOOLEAN MODE) AS SCORE FROM users WHERE MATCH (name) AGAINST ('a*' IN BOOLEAN MODE) ORDER BY SCORE, popularity DESC LIMIT 4

更新:我最后使用了这样的东西:SELECT *， MATCH (name) AGAINST (a* IN BOOLEAN MODE)作为用户的分数，其中MATCH (name) to (a* IN BOOLEAN MODE)按分数排序，popular DESC LIMIT 4

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?

我想改变的一件事，不是按分数顺序，然后按我的字段流行度，而是按一个简单的权重函数排序，比如0.5*SCORE + 0.5*popular度。如何?

1 个解决方案

#1

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

LIKE不使用全文索引。要使用全文索引，您必须使用match(如您所说):

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

The MySQL Manual has a extensive Chapter on Full-Text Search Functions.

MySQL手册中有很多关于全文搜索功能的章节。

#1