mysql like% query is slow with full-text index

user_415603 0
Viewed 11024

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?

mysql database
2 Answers

The above answer is correct but I want to add that if you are just going to filter by a field starting with like this

WHERE field LIKE 'aaa%' ORDER BY field2

then a normal index would work.

If you want to search by contains (i.e. field like '%aaa%'), then you will have to use a full-text index as mentioned above.

user_18205960
Related