Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
818 views
in Technique[技术] by (71.8m points)

database - mysql like% query is slow with full-text index

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

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"

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...