在MEDIUMTEXT上使用MySQL进行全字搜索

Question

I know this question was discussed a lot of times. Anyway, I would like to figure out again.

Well, I have table "articles" contains these fields:

• title (varchar 255)
• keywords (varchar 255)
• content_body_1 (mediumtext)
• content_body_2 (mediumtext)

There is an index on "title" and "keywords". However, there is no index on MEDIUMTEXT fields.

I need to perform "whole word" search on all these fields. I am now doing this using REGEXP:

SELECT * FROM `articles` WHERE `content_body_1` REGEXP '[[:<:]]"keyword"[[:>:]]'

And so on. It's okay for 100 articles, but it's VERY slow (2-3 seconds) on 1000 articles. REGEXP does not use indexing in MySQL. What if I have 10000 articles? Is there any way for faster search by whole keyword?

How can I get that? Is FULLTEXT much faster? If yes - how can I design my database? And also what should I do with FULLTEXT limit of minimum characters to search?

Thanks.

Answer 1

Why are you using a regexp for a full text search? You could just as easily use the % character and it's probably much faster than doing a regex.

SELECT * FROM articles WHERE content_body_1 LIKE '%keyword%'

This will find any rows where your content_body_1 contains the keyword somewhere in it.